Using LEFT, MID, or RIGHT within VLOOKUP or INDEX/MATCH/MATCH

[eszeein2]

I am trying to lookup or index only part of a cell, but I keep getting an #N/A error.

When I use VLOOKUP, I want my lookup_value to be only the LEFT 4 characters.
Alternatively, when I use INDEX/MATCH/MATCH, I want my first MATCH look_up value to be only the LEFT 4 characters.

I have tried this two ways with both functions: using the LEFT function directly into the other formulas,
=INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(LEFT(C2,4),'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))
and also creating a column to return the LEFT 4 characters and using that column in my formula.
*C2 example: 5562-3327* *Formula in D2 =LEFT(C2,4)* =INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(D2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))

Any way around this? When I physically type in the text (example: 5562) and use that cell in my formula, it works, so the functions are working properly.
=INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(D2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))

But when I try use to use LEFT function, it gives me an #N/A error, when using either VLOOKUP or INDEX/MATCH/MATCH functions

[Pete_UK]

If you have the final parameter set to zero in a MATCH function, you are looking for an exact match, but if you are taking the first 4 characters then you will only get an exact match if the lookup range contains those 4 characters. But, you can use a wildcard character, so I think this is what you are trying to do:

=INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(LEFT(C2,4)&"*",'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))

Hope this helps.

Pete

[Ace_XL]

Try

=INDEX('[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$30,MATCH(LEFT(C2,4)*1,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$A$30,0),MATCH(F2,'[Casual Cushion Fabric Grade List Prices.xlsx]Sheet1'!$A$4:$Q$4,0))

This will convert the 4 characters to number rather than text and the MATCH function should work then

[Richard Buttrey]

Hi, and welcome to the forum.

Upload your workbook so that we can see the request in context.

[MrShorty]

It might be difficult without a sample file to see exactly what is happening.

A first guess -- the LEFT(C2,4) function will return the text string "5562". The text string "5562" is not the same thing as the number 5562, and lookup functions will often not connect the two. My first guess is that you are facing one of these "numbers stored as text" kind of problems. You will need to look at your lookup value and your lookup table and see if you are searching for text strings or numbers and adapt formulas accordingly.

[eszeein2]

Using the *1 to convert the string to a number did the trick!!!! Thank you Ace_XL!