CODEWITHSUNDEEP
excelexcel-formula
rajendra kodavaty
rajendra kodavaty

Reputation: 13

lookup value based on partial value

I have a set of batch numbers in a sheet which are alphanumeric code as follows 

sdc234
fgh345
ght587
jki876

The alphabets of the batch number represent a product code. For example

sdc = 20499999
fgh = 45999999
ght = 67999992
jki = 56700000

The above relation is in another sheets. I want to match product code with batch number directly. How do i lookup a product code based on this partial info ?

Upvotes: 0

Views: 59

Answers (1)

Nitesh Halai
Nitesh Halai

Reputation: 927

You can sort your second table in an alphabetical order and use VLOOKUP with TRUE (approximate match) as your third argument.

Assuming the second table is in column A and B:

D       E
sdc234  =VLOOKUP(D1,A:B,2,TRUE)
fgh345  =VLOOKUP(D2,A:B,2,TRUE)
ght587  =VLOOKUP(D3,A:B,2,TRUE)
jki876  =VLOOKUP(D4,A:B,2,TRUE)

The output is as below:

D       E
sdc234  20499999
fgh345  45999999
ght587  67999992
jki876  56700000

EDIT: Assuming your product code is always 3 letters, you can use the LEFT function to get the first 3 letters and then use that as the lookup value. This way you can use the exact match as your third argument:

sdc234  =VLOOKUP(LEFT(D1,3),A:B,2,FALSE)
fgh345  =VLOOKUP(LEFT(D2,3),A:B,2,FALSE)
ght587  =VLOOKUP(LEFT(D3,3),A:B,2,FALSE)
jki876  =VLOOKUP(LEFT(D4,3),A:B,2,FALSE)

Credits to Mladen Savic's comment for making me think of this solution.

Upvotes: 1

Related Questions