Index and Match returning #NA - Help please!

Formula works in cell i4 but not in i3,i8 and i9
...and I don;t understand why.

Can someone help with this and explain so I can learn?

Thanks in advance for any help

File attached

You have this formula in I5, and it works:

=INDEX(Member_List!C6:C9,MATCH(I4,Member_List!O6:O9,0))

It is looking to see if the value in I4 can be found in column O (both marked in red), and if so it returns the corresponding value from column C (marked in blue), which is the MemNo column in your data.

However, the formula you have in I3 is this:

=INDEX(Member_List!C6:C9,MATCH(I4,Member_List!N6:N9,0))

Here you are looking to see if I4 exists in column N, and if so return the value in column C. What you are really trying to do is to see if I4 exists in column O (as before), and if so return the value from column N, so you need to do it this way:

=INDEX(Member_List!N$6:N$9,MATCH(I4,Member_List!$O$6:$O$9,0))

Can you see how to apply this to your other cells?

Hope this helps.

Pete

Thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete

Thanks very much to all of you - my problem is now solved.
You can call it 'a seniors moment, Brain F@@T or too early' - started on this at 2am
Each reply gave me good help so congratulations to all.

Michael

Thanks for the rep! Glad we could help.