Formula to return cell result when 2 criteria are met in a dynamic expanding name range.

Complexart · July 21, 2021, 5:04am

Formula to return cell result when 2 criteria are met in a dynamic expanding named range, the formula will match data in Column A & B and return the result in C. Example: Match Green and Small return 6/10

         A          B             C
1    Red         Big           5/10
2    Green       Small         6/10
3    Blue        Medium        8/15
4    Green       Big           11/50

I have tried sumproducts but hit a rock block when column A have repeated data. Thank you for your help.

Edit: layout for clarity

JohnSUN · July 21, 2021, 5:27am

=INDEX(C1:C1000;MATCH("Green|Small";A1:A1000&"|"&B1:B1000;0))?

Complexart · July 21, 2021, 6:03am

Thank you John, But when I replace"Green|Small" with cell “Green” and “Small” it gave me Formula parse error.

Complexart · July 21, 2021, 6:25am

=INDEX(C1:C1000;MATCH(D1&"|"&D2;A1:A1000&"|"&B1:B1000;0))

Credits to JohnSun, this worked! Thank you. By the way what does “|” means?

JohnSUN · July 21, 2021, 6:38am

This is any character that does not appear in cell texts. It is added to avoid ambiguity when concatenating strings (rare, but it’s better to play it safe). You could use # or ^ or any other

Complexart · July 21, 2021, 6:40am

and “thank|you” for the lesson.