Index & Match returning incorrect value. Arrays fixed and exact match used.

**SDes** · 09-07-2012, 10:31 AM

Hi,

I have used the INDEX and MATCH functions together for many months with no problems, however today it is returning some incorrect values and I cannot see what the problem is. I have attached a spreadsheet with an example of the data and the formula I am using.

Basically I have identified individuals (column A) which have 2 or fewer corresponding numbers in column B. I would like excel to return the value in column C which corresponds to the correct ID and Order number. Column H (order 3) should always return a #N/A value however (for example) ID number 81 (which only has 1 order) keeps returning three values. This problem disappears if I remove the number 8's in column A.

Any help would be much appreciated.

Thanks,

Suzanne

**twiggywales** · 09-07-2012, 10:37 AM

its not really clear what you need to do. it may be eaiser to start from scatch.

from your main list (A7:C947) what do you need to do with it.count what and get what infomation where

**dilipandey** · 09-07-2012, 10:50 AM

Hi Suzanne,

Welcome to the forum.

I have checked the workbook and I did not found any issues...since you are matching two items in column A&B and picking the results from column C... and the formula is doing this.. what else do you want ? where you are seeing issue ? Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**JosephP** · 09-07-2012, 11:26 AM

you need a delimiter-your match is matching 8 and 12 and not 81 and 2
change the formula to
=INDEX($C$7:$C$949,MATCH($E7&"|"&F$6,$A$7:$A$949&"|"&$B$7:$B$949,0))
for instance

**SDes** · 09-07-2012, 11:34 AM

Thank you so much! This has worked. I have never heard of such a thing. I've used this formula so many times and have never had this issue. Now to make a nice 3D chart with the results...

**SDes** · 09-07-2012, 11:36 AM

Thank you for replying, the issue has now been solved.

**JosephP** · 09-07-2012, 11:42 AM

if you are concatenating columns to make a key I recommend you always use a delimiter to avoid this sort of issue

please make it a 3D exploding pie chart with shadows, glows and bevels-that's my favorite ;-)

**Cutter** · 09-07-2012, 08:29 PM

@ SDes

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. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
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.