Question 1 of 10
The first is the lookup value, or the value XLOOKUP searches for. The second is the lookup range, or the range of cells where XLOOKUP searches for the lookup value. The third is the return range, or the corresponding range of cells which contains the value that XLOOKUP will return.
Type the following formula to find the number of scenes Andy was in:
=XLOOKUP("Andy", A:A, C:C)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | CHARACTER | EPISODES | SCENES | ||
| 2 | Michael | 137 | 3033 | ||
| 3 | Dwight | 186 | 2367 | ||
| 4 | Jim | 185 | 2150 | ||
| 5 | Pam | 182 | 1922 | ||
| 6 | Andy | 144 | 1341 |
You are typing into E2. Refer to the cells that hold the data.
XLOOKUP Function - Free Excel Practice Online
XLOOKUP is the newer, simpler cousin of VLOOKUP, and it needs three inputs: what to find, where to look and what to return. This exercise starts with a table of TV characters (how many episodes Pam appeared in, which character had 3,033 scenes) and then switches to a table of rappers.
Along the way you use the two things VLOOKUP cannot do: search from right to left, and search across a row the way HLOOKUP does. The last steps ask for a first name, a birth city and the rapper born in New Orleans, so you practise returning a different column every time.
What you'll practice in this XLOOKUP Function lesson
- The XLOOKUP function is similar to VLOOKUP and has 3 required inputs.The first is the lookup value, or the value XLOOKUP searches for. The second is the lookup range, or the range of cells where XLOOKUP searches for the lookup value. The third is the return range, or the corresponding range of cells which contains the value that XLOOKUP will return.Type the following formula to find the number of scenes Andy was in:=XLOOKUP("Andy", A:A, C:C)
- While VLOOKUP can only search from left to right, XLOOKUP can search from right to left.Type the following formula to find out which character appeared in 2367 scenes:=XLOOKUP(2367, C:C, A:A)
- Write an XLOOKUP formula to find the number of episodes that Pam appeared in.
- Write an XLOOKUP formula to find the name of the character who appeared in 3033 scenes.
- XLOOKUP also works for horizontal searches, like HLOOKUP. Write the following formula to find out Snoop Dogg's birth year:=XLOOKUP("Snoop Dogg", 1:1, 3:3)
- Write an XLOOKUP formula to get Lil Wayne's birth city.
- Write an XLOOKUP formula to get Tupac's first name.
- Write an XLOOKUP formula to return the first name of the rapper whose birth city is New Orleans.
- Write an XLOOKUP formula to return the first name of the rapper who was born in 1969.
- Write an XLOOKUP formula to return the birth city of the rapper who was born in 1969.
XLOOKUP Function practice - FAQ
How do I practice XLOOKUP Function in Excel?
Open this free LogicExcel exercise and work through 10 interactive steps. You type the real formula into a live spreadsheet grid and get instant feedback on whether it is correct, plus an explanation - no Excel install and no signup needed.
Is the XLOOKUP Function exercise free?
Yes. Every LogicExcel exercise is 100% free with no account required. Your progress saves automatically in your browser.
What will I learn in this XLOOKUP Function lesson?
This lesson covers XLOOKUP Function as part of the Lookup Functions track. You practice the syntax step by step until it becomes muscle memory you can use at work.
Is XLOOKUP better than VLOOKUP?
XLOOKUP can search right to left and across rows, needs no column number and can return your own message when nothing matches. It is available in Excel 2021 and Microsoft 365, so on an older version of Excel you still need VLOOKUP or INDEX MATCH.