VLOOKUP plus (nesting?) LEFT

Sunvisor

Board Regular
Joined
Oct 9, 2009
Messages
233
I have a LEFT formula looking at the first 3 letters of Serial numbers. I have a second sheet with Locations that correspond to the first three letters of the serial number...So I have a column showing me the Abbreviation...then another column using a Vlookup to find the corresponding Location...is there a way to combine the left formula and the Vlookup into one formula?

basically...I want it to "look for the abbreviation, then with the abbreviation do a vlookup on the second page and find the corresponding location"

Can anyone help?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
I have a LEFT formula looking at the first 3 letters of Serial numbers. I have a second sheet with Locations that correspond to the first three letters of the serial number...So I have a column showing me the Abbreviation...then another column using a Vlookup to find the corresponding Location...is there a way to combine the left formula and the Vlookup into one formula?

basically...I want it to "look for the abbreviation, then with the abbreviation do a vlookup on the second page and find the corresponding location"

Can anyone help?
Care to post your formulas with LEFT and VLOOKUP?
 

Sunvisor

Board Regular
Joined
Oct 9, 2009
Messages
233
Sheet one...column A B C

A has NYP8374 (this is a serial)
B has Left(A2,3) ...which equals "NYP"
Column C has Vlookup (B3,"Sheet two column a to b$",2,FALSE)..which equals NY Plaza


Sheet two

Column A Column B

NYP NY Plaza
BRW Broadway


I am trying to create a forumla I can do this all in one shot...I am just interested, I could just keep all the columns but I would like to start nesting.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
One of...

=INDEX(Sheet2!$B$2:$B$400,MATCH(LEFT(A2,3),Sheet2!$A$2:$A$400,0))

=VLOOKUP(LEFT(A2,3),Sheet2!$A$2:$B$400,2,0)

Adjust the ranges to suit.
 

Forum statistics

Threads
1,085,294
Messages
5,382,755
Members
401,804
Latest member
RB85

Some videos you may like

This Week's Hot Topics

Top