excel MATCH function Combining MATCH with INDEX


Example

Say, you have a dataset consisting of names and email addresses. Now in another dataset, you just have the email address and wish to find the appropriate first name that belongs to that email address.

Reverse matching email address to first name

The MATCH function returns the appropriate row the email is at, and the INDEX function selects it. Similarly, this can be done for columns as well. When a value cannot be found, it will return an #N/A error.

This is very similar behaviour to VLOOKUP OR HLOOKUP, but much faster and combines both previous functions in one.

  • Search for cell F2 value (alexander.baier@example.com)
  • Within dataset $D$2:$D$26
  • Use exact matching (0)
  • Use the resulting relative row number (14) from a different dataset $B$2:$B$26