Matching 8th Digit of Vin to Table

I finally figured this one out on my own thanks to maras_mak for helping me with my previous spreadsheet and informing me about Index and Match functions.

However I'd like to know if this was the most efficient way to do it, or if there is a simpler method.

I have a table that lists vehicles, their vehicle identification numbers (VIN), their engine, and a couple other criteria related to the engine so I don't have to remember it. The criteria was easy enough to generate with a few nested IF functions, but I had to lookup and manually enter every engine code. Since I'm only working with a single manufacturer, I figured I could set up a table and automate it.

The formula I came up with pulls the 8th digit from a vin, and looks up the matching character in a table that lists the characters and engine codes. The issue I ran into was that it wasn't playing nicely with just the MATCH and MID function because there was both text and numbers. I eventually came up with this solution that uses an IF statement, ISNUMBER, and VALUE, and now the numbers play nicely.

Formula:

=

INDEX(

Table4,

MATCH(

IF(

ISNUMBER(VALUE(MID(Table1[Vin],8,1))),

(VALUE(MID(Table1[Vin],8,1))),

MID(Table1[Vin],8,1)),

Table4[8th Digit of Vin],0),

2)

Is this the best solution or is there a better way? Sample sheet is attached. Formula is in C2.

Looks like the issue was when you extract the 8th string, the string data type is treated as text. If I were you, I would just insert a helper column to the reference table to convert all the lookup values to string. =TEXT([@[8th Digit of Vin]],"@") Then a simple INDEX/MATCH should return the correct values. =INDEX(Table4[Eng Code],MATCH(MID(Table1[@Vin],8,1),Table4[X],0))

Would you mind to use Array formula ?

=
INDEX(
Table4,
MATCH(
MID(A2,8,1),
TEXT(Table4[8th Digit of Vin],"0"),0),
2)

Press Ctrl-Shift-Enter
PS: Use A2 instead of Table1[Vin]


Regards.

This non-array formula also works:

=IFNA(VLOOKUP(MID(Table1[@Vin],8,1),Table4,2,0),VLOOKUP(MID(Table1[@Vin],8,1)+0,Table4,2,0))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar or even the same as this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for? Newest could be one of several things ...

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.