INDEX & MATCH with multiple Criteria

Question

I have been trying to figure it out for what I was doing wrong

Here the explanation for what I am currently doing :

It is a game that customer need to guess for 4 condition (outfit,brand,color,price)
I have to match between 2 worksheets which is customer's guessing and list (which is criteria)
it going to show only 2 result (bingo,try again like in yellow column)
the criteria is guessing for outfit,brand,color need to be exactly correct (as shown in list-worksheet)
for price it can be the other values but not less/more than 5

=IF(ABS(INDEX(list!D:D,MATCH(A2,list!A:A,0),MATCH(B2,list!B:B,0),MATCH(C2,list!C:C,0))-D2)<=5,"bingo",IF(ABS(INDEX(list!F:F,MATCH(A2,list!A:A,0),MATCH(B2,list!B:B,0),MATCH(C2,list!E:E,0))-D2)<=5,"bingo","try again"))

the above is formula that I use but it turned out error
if doing correctly the result would be like in yellow column
Note column and orange highlights does not involve it's just optional explanation for your understanding

list

tigeravatar · Accepted Answer · 2022-06-14 15:45:32Z

1

So the main reason you're running into issues is because your list worksheet is setup terribly. Excel runs best with a flat-file type data structure. It should be reformatted to this:

Then you can setup your guessing game and use a simple SUMIFS formula:

In cell E2 of the guessing game worksheet, the formula is:

=IF(ABS(D2-SUMIFS(list!D:D,list!A:A,A2,list!B:B,B2,list!C:C,C2))<5,"Bingo!","Try again")

answered Jun 14, 2022 at 15:45

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chris2004 Over a year ago

Thank you so much! if let's say the worksheets are not allowed to change. is there other way.

tigeravatar Over a year ago

@Helloworld I would really recommend adjusting the list sheet's data setup to match the flat-file structure as shown. The way it currently is will only cause headaches, especially if more data gets added. The recommended way will scale easily as more data is added. While possible to use a formula to achieve the desired results using your shown data setup, it would be hugely unwieldy and prone to breaking

Collectives™ on Stack Overflow

INDEX & MATCH with multiple Criteria

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related