Reputation: 87
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
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.
Upvotes: 0
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