Index, Match function giving #N/A error

[ammykhan]

​

Learning vlookup, match, index function as a beginner in excel, trying to use the match function together with index function, but surprisingly without any apparent reason it is giving the error, although the same problem can be solved by using vlookup with choose function, is this the limitation of match, index function that is giving the error or I'm missing a trick to do it the right way, pls help, thanks attaching the data through mini sheet here below.

W1_V1 DataValidation.xlsx
	B
13	#N/A
Sheet1

Cell Formulas
Range	Formula
B13	B13	=INDEX(A2:C6,MATCH(B9,A2:A6,FALSE),1)

 

[ammykhan]

 

[ammykhan]

My excel sheet appears like this for the query asked

Name	Grade	Sales
Garfield	A	Bed Sheets
Felicia	B	Towels
Bosco	C	Vehicle
Helen	A	Grocery
Flash	B	Wheels
									Towels
	Towels
	#N/A​

 

[Fluff]

You are using the match on the wrong column, it should be

=INDEX(A2:C6,MATCH(B9,C2:C6,FALSE),1)

 

[ammykhan]

Yes, may be the but here the data is structured in such a way that I'm required to find data on the left, and i hear that it is the advantage of combining the Match Index function that it provides us the option to do left look up as seen here in the given example.

 

[Fluff]

I'm required to find data on the left

That is exactly what it does.

 

[ammykhan]

Here, I want to use the Sales data to extract the corresponding names of the seller, "=VLOOKUP(B9,CHOOSE({1,2},C2:C6,A2:A6),2,TRUE)" give us the result but not so the MATCH INDEX function, interested to see whether it is the limitation of this function or there is something wrong with my structured formula, because we know that INDEX MATCH function is capable to do left lookup unlike VLOOKUP function

 

[Fluff]

Have you tried the formula I suggested?

 

[ammykhan]

No I think the formula you told may not work here, my search is B9 which is the Sales item i.e Towels and want to extract the seller name. Here, I need to extract data from A2:A6 and not from C2:C6, so my match function need to lookup data in A2:A6 instead of C2:C6

 

[Fluff]

so my match function need to lookup data in A2:A6 instead of C2:C6

NO it does not. What is the point in asking for help, if you don't actually try what is suggested?