VLookup With LEFT Function (2002 SP3)

Topic

New Reply

Is it possible to do a Vlookup on a specific set of characters in a string? A sample string would be Project=605 (), Cost Center=61205, where I want to do the lookup on the project number – 605 in this case. The number will always be in the same position. I want to lookup the name of the project in a list. I think there is a way to do a LEFT function and ask for specific positions (9-11 in this case) but haven’t been able to figure it out. I’m thinking it would be a Vlookup(Left(….. ??

TIA

Reply | Quote

Replies

The MID() function is probably what you are looking for. It takes 2 arguments, the starting position and the length (number of characters). In your exampole MID(“Project=605”,9,3) would return 605. This value would be a string so you may need to either convert to a numeric withh the VALUE() function, or ensure the lookup numbers are stored as text. To handle “Cost Center=61205”, you would need to pass the MID function different arguments, 13 and 5.

Andrew C

Reply | Quote

The LEFT(), MID(), and RIGHT() functions can not work with arrays, therefore, if your table has that string in it you will need to add a couple of columns to your table. I have attached a workbook that shows how to do it. Note that columns B:C and F:G are formatted as text. The VLOOKUP formula are in H1:H2.

Reply | Quote

I have modified your example to use array formulas with MID() and RIGHT(). It uses the INDEX / MATCH combination instead of VLOOKUP in the array formula to achieve the same result.

Reply | Quote

To make this formula more dynamic, in case the length of the lookup project number is not consistent, you can substitute Len(F1) for the ‘3’ in the first formula, or Len(G1) for the ‘5’ in the second formula – still entered as arrays, of course.

=INDEX(A1:D12,MATCH(F1,MID(A1:A12,9,LEN(F1)),0),4)
=INDEX(A1:D12,MATCH(G1,RIGHT(A1:A12,LEN(G1)),0),4)

Reply | Quote

Tony and Ban, I am not familiar with Index so now I am going to study what you sent and see if I can use it elsewhere. I love learning new stuff especially in Excel.

Thanks for the input!

Reply | Quote

Legare and Andrew, the MID() was what I was looking for and I was able to quickly get the info I needed. I knew I knew what it was, just couldn’t remember

Thanks.

Reply | Quote