Index Match - Data Validation List - Result N/A

**Veloso** · 07-21-2012, 10:29 AM

Hi Excel smarts, I have a problem I hope you can help me.

What I have below works fine

A1: Name B1: Yes/No

A2: abc B2: Yes
A3: bcd B3: No
A4: cde B4: Yes
A5: def B5: No
A6: efg B6: Yes
A7:
A8: bcd B8: No

(A8) Data Validation List: =A2:A6
(B8): =INDEX(B2:B6,MATCH(A8,A2:A6),0)
All formatted as Text.

My problem is when I change (A2) to "cbc" and look-up (A8) list again I get (B8) N/A. look below.

A1: Name B1: Yes/No

A2: cbc B2: Yes
A3: bcd B3: No
A4: cde B4: Yes
A5: def B5: No
A6: efg B6: Yes
A7:
A8: bcd B8: N/A

It seems changing (A2) with a beginning of any alphabetic letter above "b" I get (B8) N/A

I hope I am not too confusing with my example. Will appreciate all the help given in solving my problem.

Thanks

**martindwilson** · 07-21-2012, 10:44 AM

=INDEX(B2:B6,MATCH(A8,A2:A6),0) needs to be MATCH(A8,A2:A6,0) for an exact match and since your index is b2:b6
you dont have to include the column number
=INDEX(B2:B6,MATCH(A8,A2:A6,0),0)
try this instead
=INDEX(B2:B6,MATCH(A8,A2:A6,0))

**Marcol** · 07-21-2012, 10:46 AM

Try this in B8

Formula:

Please Login or Register  to view this content.

[EDIT]
Martin beat me to it, see his explanation post #2

**Veloso** · 07-21-2012, 10:51 AM

Thanks, thanks so much. I have spent a lot of time trying to solve my problem, I should have asked the experts.

Thanks again.

**Cutter** · 07-21-2012, 12:49 PM

Hello Veloso, and welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself.
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.