When there is only one lookup value, many people know to use VLOOKUP. But if there are two or three lookup values, can VLOOKUP still work? How do you set the lookup values?
Multi-criteria lookup
This time we won't use VLOOKUP; we'll use its sibling, LOOKUP!
=LOOKUP(1,0/((E2=A2:A7)*(F2=B2:B7)),C2:C7)
=LOOKUP(1,0/((E2=A2:A7)*(F2=B2:B7)),C2:C7)
- Start from the middle: verify whether E2 equals each value in A2:A7. In other words, check whether A2:A7 equals "中環" — TRUE if yes, otherwise FALSE.
- Similarly, check whether B2:B7 equals "東涌" — TRUE if yes, otherwise FALSE.
- At this point Excel will have two arrays of TRUE and FALSE, and they are multiplied together, like this:
- Since TRUE=1 and FALSE=0, only one pair TRUE*TRUE produces 1. The rest are divided by 0, as follows:
- Because any number divided by 0 results in an error and cannot be compared to 1, only the entry that is divided by 1 survives. Comparing that to 1 then naturally returns $80.
