CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2013
Sayam Nandy
Sayam Nandy

Reputation: 87

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

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?

Upvotes: 2

Views: 24215

Answers (2)

HackSlash
HackSlash

Reputation: 5831

Problem: There is an inconsistency in the match type and the sorting order of the data

When you use MATCH, there should be a consistency between the value in the match_type argument and the sorting order of the values in the lookup array. If the syntax deviates from the following rules, you will see the #N/A error.

If match_type is 1 or not specified, the values in lookup_array should be in an ascending order. For example, -2, -1, 0 , 1 , 2…, A, B, C…, FALSE, TRUE, to name a few.

If match_type is -1, the values in lookup_array should be in a descending order.

SOLUTION: Either change the match_type argument to 1, or sort the table in descending format. Then try it again.

SOURCE: https://support.office.com/en-us/article/how-to-correct-a-n-a-error-in-index-match-functions-f91874c9-d30b-4b7a-8a6b-c622764a1992

Upvotes: 0

ian0411
ian0411

Reputation: 4275

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.

Upvotes: 2

Related Questions