VLOOKUP by ID

Intermediate← PreviousSkip →
Exercise 2 of 11VLOOKUP

Which department is employee E106 in? Use VLOOKUP.

Write your formula in the green cell, J2.

Lookups

Find a salaryVLOOKUP by IDVLOOKUP a numberLook up a dateINDEX and MATCHMATCH on its ownLook to the leftChain two lookupsLookup inside a calculationWhen it's not thereTwo-way lookup
staff-list.xlsx
fx
1IDNameDepartmentSalaryStart dateDepartmentBonus
2E101Hannah LeeSales$52,000.00Mar 15, 2021Sales10%Department
3E102Omar HaddadFinance$61,000.00Jul 1, 2019Finance6%
4E103Grace KimMarketing$48,500.00Jan 9, 2023Marketing8%
5E104Diego RuizSales$57,500.00Nov 23, 2020IT7%
6E105Priya ShahIT$72,000.00May 14, 2018
7E106Tom BeckerFinance$66,000.00Sep 5, 2022
8E107Lena FischerIT$69,500.00Feb 19, 2024
9E108Sam OkaforMarketing$51,000.00Aug 30, 2021
10
11

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

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.

GuideHow to Use VLOOKUP in Excel, Step by Step→

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 →