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]

Dear, my lookup column is Column A, how it will give me the result if I have mentioned the wrong column in MATCH function, it is futile for me to try it as I know it will not give me the result, the formula below does exactly the same i.e it is giving me Felicia, the seller name which is what I need to find, MATCH(B9,C2:C6,FALSE) will lookup the data in Column C i.e the Sales List

=VLOOKUP(B9, CHOOSE({1,2}, C2:C6, A2:A6),2,FALSE)

 

[Fluff]

it is futile for me to try it as I know it will not give me the result

If you know that so well, why did you ask the question?
If you want help, then please try what is suggested, rather than dismissing it out of hand as you know better than us.
As I have said before the formula does EXACTLY what you have asked.

 

[ammykhan]

Upon your insistence I tried it and as expected I'm getting #REF error, attaching the ss for your reference.

 

[Fluff]

That is not the formula I suggested, you are missing the 1 from the end.

 

[Fluff]

The other option is

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

Both formulae do the same thing.

 

[ammykhan]

Dear sir, I'm asking to get clarification regarding the MATCH INDEX function, it is annoying for us to get #N/A we're curious to get rid of the this nasty error. I asked that although Match Index function enable us to lookup on the left side, then why it is returning the #N/A error, I'm confused about the usage of MATCH INDEX function, to get my answer I need support from the Excel guru like you to help me out.

 

[ammykhan]

OK, yes thank you it worked, you're right I got it now ,thank you so much dear

 

[Fluff]

Glad to help & thanks for the feedback.

 

[ammykhan]

I didn't see the 1 at the end, which made me believe that it will not give me the required result. thanks for the help