One solution is to concatenate the two cells in the source and lookup tables. Another is to leverage INDEX and MATCH.
When entering this formual, because it's an array formula, you must enter it by using CTRL-SHFT-Enter, instead of just Enter.=INDEX(E2:E1000,MATCH(1,(C:C=A2)*(D:D=B2),0),0)
Here's how this works:
- The MATCH syntax is
MATCH(lookup_value, lookup_array, [match_type])where match type is 0 = exact, 1 is less than, and -1 is greater than.When the match occurs, it will return the relative position of the match within the range.
- In our MATCH formula, it's looking for an exact match to 1 (lookup_value = 1).
- If the value of A2 is found in column C, a value of 1 is returned, otherwise a 0 is returned.
- Likewise for the value of B2 in column D.
- When multiplied together, if a matching value in columns C and D on the same row match the values from A2 and B2, then a 1 is returned, else we get a 0 (lookup_array).
- The INDEX syntax is
INDEX(array, row_num, [column_num])
- When MATCH returns a value of the matching row, we get the value in column E for the corresponding row.