Why is my index match function is returning #N/A Error?

Question

I don't know why I am getting #N/A error while I am using index match function.

I have two data sets in different sheets:

Vehicle group code  Vehicle model   Vehicle code    Vehicle group   Vehicle make
41                    004             41004          Standerd           
61                    007             61007          Sports 
41                    007             41007          Standerd    
51                    005             51005          Premium    
41                    004             41004          Standerd   

I have to fill the "vehicle make" column by extracting the information from the below data-set, which resides on another sheet.

Vehicle code    Vehicle group   Vehicle make    Vehicle model
41004           Standard          Toyota        Corolla 
41005           Standard          Honda         Jazz    
41006           Standard          Hyundai       i30 
41007           Standard          Mazda         3   
51004           Premium           BMW           320i

So, in that case, I am using index match function by taking the vehicle code as the lookup value.

Here is my index function:

=INDEX(
    'Vehicle details'!A1:F13,
    MATCH('Policy data'!F2,'Vehicle details'!A1:A13,0),
    MATCH('Policy data'!H1,'Vehicle details'!A1:F1,0)
)

and it is returning #N/A error.

I tried a different way, with two datasets in two sheets, and wrote following index match function:

=INDEX(
    Y1:AD13,
    MATCH(F2,Y1:Y13,0),
    MATCH(G1,Y1:AD1,0)
)

In spite of that, it is returning #N/A error.

Can you please show me what I am doing wrong?

Solution

Here is the INDEX/MATCH formula you can use to retrieve your results:

=IFERROR(INDEX($K$2:$K$6,MATCH(C2,$I$2:$I$6,0)),"")

Or if you want to use VLOOKUP, the formula will be:

=IFERROR(VLOOKUP(C2,$I$2:$K$6,3,0),"")

Adjust your ranges accordingly based on your data structure. But let me know if you need help.

Comments

[QHarr]

Why can't you use VLookup? Vlookup(vehiclecode in top table, range of data in second table, 3,False)

[YowE3K]

If I (a) copy your first set of data to cells D1:G6, (b) copy your second set of data to Y1:AB6, (c) enter your second formula into cell H2, (d) change the G1 in the formula to H1, then I get a value of "Toyota". (Without the edit to the formula, I get a value of "Standard" because it is looking for the "Vehicle group" heading from G1.)

[Sayam Nandy]

It seems that you have kept those two datasets into same sheets instead of the different sheet.However, i tried your formula.but got the same error.

[Sayam Nandy]

i have written =+IFERROR(INDEX('Vehicle details'!C1:C13,MATCH('Policy data'!F2,'Vehicle details'!A1:A13,0),MATCH(H1,'Vehicle details'!A1:F1,0)),"NA ")

[Sayam Nandy]

vlookup formula,=+IFERROR(VLOOKUP(F2,'Vehicle details'!A1:F13,MATCH('Policy data'!H1,'Vehicle details'!A1:F1,0),FALSE)," Not getting")

[ian0411]

I am so confused with your formulas. Can you tell me what are the sheets' names for both tables or maybe upload some screenshots so I know how you set up your data? Guess this will be easier for me to show you what the formula exactly you should use.