Match 5 criteria Formula

[bwmuhich]

Hi excel forum.

I am trying to find a formula that will return an indexed results range given 5 matching criteria. I tried index/match but I could only do up to 3 nested match functions before the formula started to give me an error.

I need to be able to paste the formula across cells AB5 - AM5 of the attached worksheet that will return the Product # in the N7:S7 range if each of the 5 values in rows 10,12,16, 18, and 19 of the N-S range match each of the 5 corresponding values of a given column in the AB-AM range.

I've attached a sample worksheet.

Thank you for your help.

Be Well,

[FDibbins]

This seems to do what you want...
=IFERROR(INDEX($N$7:$S$7,MATCH(AB10&AB12&AB16&AB18&AB19,INDEX($N$10:$S$10&$N$12:$S$12&$N$16:$S$16&$N$18:$S$18&$N$19:$S$19,0),0)),"")
copied across

[bwmuhich]

Thank you!

[FDibbins]

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. 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 relatively 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.

[bwmuhich]

I tried to add reputation but it said I have to spread some reputation around before giving you more.

I did mark this thread as solved but have reopened it since I was unable to get this formula to work when I added additional criteria. Your formula works perfect as initially requested, but I wanted to change it into an IF formula and nest several different if conditions in the same formula, but when I did this, only the first if formula functions correctly. I wanted to be able to modify this formula where it would return the product number in the N7:S7 range if it matched all 5 criteria, but I wanted it to return a "similar Plan 1" result if 4 of the 5 criteria matched for rows 10, 12, 16 and 18, and then a third nested if condition if 3 of the 5 criteria matched, the 3 matching criteria being in rows 10, 12 and 16.

[FDibbins]

That formula is looking at matching - exactly - the data in N10:S10 with AB10 and N11:S11 with AB11 etc if there is no exact match, it will error. It will error too, if they are not in the same sequence.

Im not sure how we would do a 4/5 or 3/5 match, because, Im assuming, that any of the 3 could match?

[bwmuhich]

That's okay. I'm able to put multiple similar formulas in other rows and hide these formula rows so if any of them return a true answer, then I can go from there.

But what would be really helpful is if this formula could be slightly modified to still give me a match if the value in row 16 is within +/- $250. The actual value in that row is a dollar value, so if there's anything within $250 of that cell value, then that criteria is still close enough to return a yes answer, versus having to match it exactly. Would we have to completely rewrite the formula or is there a way to just modify the row 16 component of this formula:

=IFERROR(INDEX($N$7:$S$7,MATCH(AB10&AB12&AB16&AB18&AB19,INDEX($N$10:$S$10&$N$12:$S$12&$N$16:$S$16&$N$18:$S$18&$N$19:$S$19,0),0)),"")