Value Needed

DebbieFost
DebbieFost used Ask the Experts™
on
I have a value as follows:

=IF(RIGHT(A2,1)="6","DRN-OC",IF(OR(RIGHT(A2,1)="3",RIGHT(A2,1)="F",RIGHT(A2,1)="C",RIGHT(A2,1)="1",RIGHT(A2,1)="4"),"DRN-SF",IF(OR(RIGHT(A2,1)="4",RIGHT(A2,1)="F",RIGHT(A2,1)="C",RIGHT(A2,1)="1"),"DRN-SF",IF(OR(RIGHT(A2,1)="5", RIGHT(A2,1)="2",RIGHT(A2,1)="L",RIGHT(A2,1)="V",RIGHT(A2,1)="J"),"DRN-SAC",IF(OR(RIGHT(A2,1)="7", RIGHT(A2,1)="8"),"DRN-DAL")))))

Which take a Account Number, look at the last 2 digits: Example "12-01324-1" and returns in another cell "DRN-SF".
 
These are the current outputs:
Branch#      Branch
1      DRN-SF
2      DRN-SAC
3      DRN-SF
4      DRN-SF
5      DRN-SAC
6      DRN-OC
7      DRN-DAL
8      DRN-DAL
9      DRN-DAL
10      DRN-DAL
12             DRN-DAL
13             DRN-DAL
14             DRN-DAL
15             DRN-DAL
Anything above 15, would also return DRN-DAL

I would like any suggestions on making this value better, currently values 9 & 10 return FALSE.

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
First, make a table like the one above, that you can lookup against.

Then, you merely need to use VLOOKUP to lookup the rightmost character(s) against that table to return the Branch.  Just make sure the numbers on the left side are text and in sorted order, otherwise we'd have to change the below formula a bit for a numeric comparison using Value(Right(G2,Len(g2)-10+1)).

If your table is in A1:B15 (for the first 15 numbers), and your account numbers start in G2, for example, you can use:

[H2]=VLOOKUP(RIGHT(G2,LEN(G2)-10+1),$A$2:$B$15,2)

Assumes that for double-digit ending characters (e.g., 10, 11, 15, 22, 100, etc.), look at the rightmost two characters.

see attached file.

Dave
lookupBranch-r1.xls
Steven CarnahanAssistant Vice President\Network Manager

Commented:
@dlmille:

Your file looks good except rows 18 and 19 return incorrect. They appear to only look at the last character ignoring the fact that they are end in 2 digits.

"Anything above 15, would also return DRN-DAL"

EDIT:  Actually all the 2 digit ones appear to ignore that fact at least on my system.
Most Valuable Expert 2012
Top Expert 2012

Commented:
My bad - for reasons I won't go into (was interrupted mid-post, lol).  Thanks for the catch.

The formula is revised to:
=VLOOKUP(VALUE(RIGHT(G2,LEN(G2)-10+1)),$A$2:$B$11,2)

PS - the vlookup table only has to end at a number where all the remaining higher numbers have the same value - so it could end at 7 in the current example.

See attached.

Dave
lookupBranch-r2.xls
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Steven CarnahanAssistant Vice President\Network Manager

Commented:
Much better.   :)  

I am still trying to understand vlookup better myself.  It appears to be much cleaner than nesting if's as the author was doing however that's probably the way I would have gone as well.
Most Valuable Expert 2012
Top Expert 2012

Commented:
The VLOOKUP function is very nice in this, as it believes the lookup_array is sorted (and recall text and numbers don't sort the same way, so I should have used the VALUE approach from the start), and if not using an exact match, VLOOKUP is the fastest, and returns the last value less than or equal to the lookup_value so if all other values being looked up are not in the table, then you get the last value in the table for the rest of the matches.

MATCH and LOOKUP have similar attributes (though LOOKUP doesn't have an exact match parameter).

In the case when you're not looking for an exact match, understand that these functions assume the data must be sorted, so you may get unintended results if its not.  And there are a lot of creative ways to use the functions without exact match and having unsorted data! Like creating dynamic ranges, for example.

Cheers,

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
FYI only, as I think the TABLE approach is the best approach for this type of problem.

The syntax for your nested if is corrected/enhanced, below, assuming the number is to the right of the second dash:

[G2]=IF(OR(RIGHT(G2,LEN(G2)-FIND("-",G2,FIND("-",G2)+1))="1",RIGHT(G2,LEN(G2)-FIND("-",G2,FIND("-",G2)+1))="3",RIGHT(G2,LEN(G2)-FIND("-",G2,FIND("-",G2)+1))="4"),"DRN-SF",IF(OR(RIGHT(G2,LEN(G2)-FIND("-",G2,FIND("-",G2)+1))="2",RIGHT(G2,LEN(G2)-FIND("-",G2,FIND("-",G2)+1))="5"),"DRN_SAC",IF(RIGHT(G2,LEN(G2)-FIND("-",G2,FIND("-",G2)+1))="6","DRN-OC","DRN-DAL")))

And copy down.

"Enhanced" because it becomes more difficult to handle two digits or more without some type of pattern matching or measurement of text length (as was done with the VLOOKUP).

Compared with the VLookup approach, this looks really complicated and much more difficult to support, especially if someone closes down or opens a new branch!

Cheers,

Dave
Most Valuable Expert 2011
Top Expert 2011

Commented:
FWIW, you could shorten the IF version to:

="DRN-"&IF(OR(MID(G2,10,LEN(G2))={"1","3","4"}),"SF",IF(OR(MID(G2,10,LEN(G2))={"2","5"}),"SAC",IF(MID(G2,10,LEN(G2))="6","OC","DAL")))

Author

Commented:
dlmille

I used the following: =VLOOKUP(VALUE(RIGHT(C2,LEN(C2)-10+1)),$A$2:$B$11,2)  which works great, except I need to put the table in a tab called "AR Cat to GL Code Converter".  Can you adjust the vlookup for me?
not for points:

=VLOOKUP(VALUE(RIGHT(C2,LEN(C2)-10+1)),'AR Cat to GL Code Converter'!$A$2:$B$11,2)
Most Valuable Expert 2012
Top Expert 2012

Commented:
That looks like my original formula except the table is on a different sheet in your example

Author

Commented:
Can you look at the value with the Tab reference, I am getting a "#VALUE!" problem.

Author

Commented:
Can you look at the value with the Tab reference, I am getting a "#VALUE!" problem.

Please help.

Thank you.
QuickBase-Affinity-Converters.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
Sorry guys, I've been traveling and missing a couple comments on my iPhone, lol.

Debbie, I've put the formula in the attached, with the tab changed to 'AR Cat to GL Code Converter', and its working.

Here's the formula, again:

=VLOOKUP(VALUE(RIGHT(G2,LEN(G2)-10+1)),'AR Cat to GL Code Converter'!$A$2:$B$11,2)

If you are getting a #VALUE! error, however, the problem is not with the reference, it is with the lookup value or table.  Make sure your table has numeric (not text) values in the left column as with the attached worksheet.

Put the following formula in and advise what value you are getting. Substitute G2 for the actual reference you're tryinig to obtain the branch from and advise what that value is:

=VALUE(RIGHT(G2,LEN(G2)-10+1))

Or better yet, upload some of the data in a spreadsheet so we can diagnose what's going on, because the lookup value is not working against the table, correctly.


Dave
lookupBranch-r2.xls

Author

Commented:
Dave,

I have attached my spreadsheet with data included.  I have tried various combination that all seem to end in a "#VALUE!" problem.

Thank you for your time.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Ok. Just go ahead and attach it and I'll take a qui k look

Dave

Author

Commented:
Here you go...

Thank you, Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Debbie - try again

Be sure the attachment is named in the post and you then have to add a description THEN hit ok

Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
Ok - the problem is the lookup value "Order Ref" has a space at the end of the text string.  Please advise how you get this data? Is it from a database extract or something?  Do I need to handle this in the formula, or can we strip that space out right now, then use the formula we have?

Thanks,

Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
For the moment, I've fixed the formula - but if you can strip the last character out, you then would use the original formula.

Here's the formula in row 2:

=VLOOKUP(VALUE(RIGHT(LEFT(C2,LEN(C2)-1),LEN(C2)-10-1+1)),'AR Cat to GL Code Converter'!$B$69:$C$78,2)

See attached,

Cheers,

Dave
QuickBase-Affinity-Converters-r1.xlsm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial