I have two sheets in Excel, Sheet1 and Sheet2.
They both contain 3 columns A, B and C.
My goal is to get values from C in Sheet2 to C in Sheet1, based on conditions comparing the values in both A and B at the same time.
A in Sheet2 contains numbers grouped together, for example 11,11,13,13,12,12. A in Sheeet1 contains some of those numbers, but not nessecarily in the same order or the same number of rows, for example 11,11,12,13,13.
B in Sheet2 also contains numbers like 2,1,1,2,1,2. B in Sheet1 again contains part of those numbers. For example, 1,2,1,1,2.
There are only unique combinations of pairs in A and B (in that specific order) for Sheet1 and Sheet2 respectively.
C in Sheet2 consists of numbers connected to the specific combination of numbers in A and B.
Now, I want to fill C in Sheet1 based on the values from C in Sheet2. For example for C1: Get the value (row x) in 'Sheet2'!Cx, so that 'Sheet1'!A1='Sheet2'!Ax, AND 'Sheet1'!B1='Sheet2'!Bx (which would be the 2nd row in this example).
I was thinking about something like
C1=INDEX('Sheet2'!C:C;...)
where
...=IF(AND(MATCH(A1;'Sheet2'!A:A;0);MATCH(B1;'Sheet2'!B:B;0));?;?)
?= I don't know what I would write here, but I would want the return value of IF be the row number where both conditions are true.
The problem is that MATCH only returns the first number in A and B respectively for which the condition is true, while I have several non-unique numbers in A. I would want to look through the whole 'Sheet2'!A:A and get all the matching values, and then look through the corresponding 'Sheet2'!B:B to check the second condition.
Or there might be a completely different take on this problem. Do someone have a suggestion on how to solve this?
