Index + Match with another check

Hi,

the table look as follows;

A B C
1 A 2 55
2 A 3 55
3 B 3 55
4 C 3 75
5 D 3 65
6 A 2 75
7 A 3 65

i have another table as follows

A B
1 A 2
2 C 3


The result should be like this:

A 2 55
C 3 75

Meaning.. Cell A1 (A) gets a match then checks in the same row on column two matching with another table B1 (2).. if both cells in both columns are matched, then C1 (55) will be returned..

=INDEX(C:C;MATCH(A1;A:A;0);MATCH(A2;B:B;0))

I can sort the whole table and use vlookup, but how can i make it without sorting ?


what am i missing here , please help?

A couple of non-array options would be:

Please Login or Register  to view this content.

If you're using XL2007 you could also think about using SUMIFS function.