Two-way lookup

Intermediate← PreviousSkip →
Exercise 11 of 11 Premium

Find the value for employee E102 in the column headed Start date. Use MATCH for both the row and the column, so the formula keeps working if the columns move.

Checking your plan…

staff-list.xlsx

ABCDE
1IDNameDepartmentSalaryStart date
2
3
4
5
6
7
8

About lookups in Excel

Lookups are the most asked-about formulas in Excel job tests. Practice finding a value in a staff list with XLOOKUP and VLOOKUP, use INDEX and MATCH together, chain two lookups, and handle values that aren't there.

Functions you can use here

  • XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • INDEX(array, row_num, [column_num])
  • MATCH(lookup_value, lookup_array, [match_type])

More lookups exercises

  1. 1.Find a salary
  2. 2.VLOOKUP by ID
  3. 3.VLOOKUP a numberPremium
  4. 4.Look up a datePremium
  5. 5.INDEX and MATCHPremium
  6. 6.MATCH on its ownPremium
  7. 7.Look to the leftPremium
  8. 8.Chain two lookupsPremium
  9. 9.Lookup inside a calculationPremium
  10. 10.When it's not therePremium
  11. 11.Two-way lookupPremium
See the whole topic →