Excel Formulas and Data Analysis Course：Lookups: XLOOKUP, VLOOKUP, INDEX/MATCH and IFERROR

1. 第1题，共15题

   What kind of match does XLOOKUP use if you leave out match_mode?

   • ANext larger item
   • BWildcard match
   • CApproximate match
   • DExact match
   显示答案

   答案: D. Exact match

   Exact match (0) is the default for XLOOKUP. If nothing matches, it returns #N/A.

2. 第2题，共15题

   In the price list below, what does =XLOOKUP("ALM-11",A2:A7,D2:D7) return?

   	A	B	C	D	E
   1	Code	Item	Category	Price	Stock
   2	CAM-01	Dome camera	Cameras	189.99	14
   3	CAM-02	Bullet camera	Cameras	149.5	0
   4	ALM-10	Door sensor	Alarms	24.75	120
   5	ALM-11	Motion sensor	Alarms	39	64
   6	LCK-05	Smart lock	Locks	229	8
   7	LCK-06	Keypad lock	Locks	119	22

   • A24.75
   • B64
   • CMotion sensor
   • D39
   显示答案

   答案: D. 39

   XLOOKUP finds ALM-11 in column A and returns the value from the same row of D2:D7, the price 39.

3. 第3题，共15题

   In the price list below, what does =XLOOKUP("Smart lock",B2:B7,A2:A7) return?

   	A	B	C	D	E
   1	Code	Item	Category	Price	Stock
   2	CAM-01	Dome camera	Cameras	189.99	14
   3	CAM-02	Bullet camera	Cameras	149.5	0
   4	ALM-10	Door sensor	Alarms	24.75	120
   5	ALM-11	Motion sensor	Alarms	39	64
   6	LCK-05	Smart lock	Locks	229	8
   7	LCK-06	Keypad lock	Locks	119	22

   • ALCK-06
   • B229
   • CLCK-05
   • D#N/A
   显示答案

   答案: C. LCK-05

   XLOOKUP can return a value from a column to the left of the lookup column. Smart lock is in row 6, and its code is LCK-05.

4. 第4题，共15题

   In the price list below, what does =VLOOKUP("LCK-06",A2:E7,4,FALSE) return?

   	A	B	C	D	E
   1	Code	Item	Category	Price	Stock
   2	CAM-01	Dome camera	Cameras	189.99	14
   3	CAM-02	Bullet camera	Cameras	149.5	0
   4	ALM-10	Door sensor	Alarms	24.75	120
   5	ALM-11	Motion sensor	Alarms	39	64
   6	LCK-05	Smart lock	Locks	229	8
   7	LCK-06	Keypad lock	Locks	119	22

   • A22
   • B119
   • C229
   • DLocks
   显示答案

   答案: B. 119

   VLOOKUP finds LCK-06 in the first column and returns column 4 of the range (Price), which is 119. FALSE asks for an exact match.

5. 第5题，共15题

   In the price list below, what does =VLOOKUP("Smart lock",A2:E7,1,FALSE) return?

   	A	B	C	D	E
   1	Code	Item	Category	Price	Stock
   2	CAM-01	Dome camera	Cameras	189.99	14
   3	CAM-02	Bullet camera	Cameras	149.5	0
   4	ALM-10	Door sensor	Alarms	24.75	120
   5	ALM-11	Motion sensor	Alarms	39	64
   6	LCK-05	Smart lock	Locks	229	8
   7	LCK-06	Keypad lock	Locks	119	22

   • ALCK-05
   • B#N/A
   • C#REF!
   • DSmart lock
   显示答案

   答案: B. #N/A

   VLOOKUP only searches the first column of the range (column A, the codes). "Smart lock" is not there, so the exact match fails with #N/A.

6. 第6题，共15题

   In the price list below, what does =VLOOKUP("ALM-10",A2:E7,6,FALSE) return?

   	A	B	C	D	E
   1	Code	Item	Category	Price	Stock
   2	CAM-01	Dome camera	Cameras	189.99	14
   3	CAM-02	Bullet camera	Cameras	149.5	0
   4	ALM-10	Door sensor	Alarms	24.75	120
   5	ALM-11	Motion sensor	Alarms	39	64
   6	LCK-05	Smart lock	Locks	229	8
   7	LCK-06	Keypad lock	Locks	119	22

   • A120
   • B0
   • C#N/A
   • D#REF!
   显示答案

   答案: D. #REF!

   The range A2:E7 has only 5 columns. When col_index_num is greater than the number of columns, VLOOKUP returns #REF!.

7. 第7题，共15题

   You write =VLOOKUP(A2,Prices,3) and leave out the last argument. What kind of match does VLOOKUP use?

   • AExact match, the same as FALSE
   • BWildcard match on the lookup value
   • CApproximate match
   • DNone; Excel shows an error until you add it
   显示答案

   答案: C. Approximate match

   If range_lookup is left out, VLOOKUP uses approximate match (TRUE), which expects the first column to be sorted. Use FALSE for an exact match, such as for codes.

8. 第8题，共15题

   In the price list below, what does =XLOOKUP("CAM-09",A2:A7,D2:D7,"Not listed") return?

   	A	B	C	D	E
   1	Code	Item	Category	Price	Stock
   2	CAM-01	Dome camera	Cameras	189.99	14
   3	CAM-02	Bullet camera	Cameras	149.5	0
   4	ALM-10	Door sensor	Alarms	24.75	120
   5	ALM-11	Motion sensor	Alarms	39	64
   6	LCK-05	Smart lock	Locks	229	8
   7	LCK-06	Keypad lock	Locks	119	22

   • A#N/A
   • B0
   • C189.99
   • DNot listed
   显示答案

   答案: D. Not listed

   CAM-09 is not in column A, so XLOOKUP returns the if_not_found value you supplied, "Not listed".

9. 第9题，共15题

   In the price list below, what does =XLOOKUP("CAM-09",A2:A7,D2:D7) return?

   	A	B	C	D	E
   1	Code	Item	Category	Price	Stock
   2	CAM-01	Dome camera	Cameras	189.99	14
   3	CAM-02	Bullet camera	Cameras	149.5	0
   4	ALM-10	Door sensor	Alarms	24.75	120
   5	ALM-11	Motion sensor	Alarms	39	64
   6	LCK-05	Smart lock	Locks	229	8
   7	LCK-06	Keypad lock	Locks	119	22

   • AAn empty cell
   • B#VALUE!
   • C#N/A
   • D0
   显示答案

   答案: C. #N/A

   With no match and no if_not_found argument, XLOOKUP returns the #N/A error.

10. 第10题，共15题

    In the price list below, what does =MATCH("LCK-05",A2:A7,0) return?

    	A	B	C	D	E
    1	Code	Item	Category	Price	Stock
    2	CAM-01	Dome camera	Cameras	189.99	14
    3	CAM-02	Bullet camera	Cameras	149.5	0
    4	ALM-10	Door sensor	Alarms	24.75	120
    5	ALM-11	Motion sensor	Alarms	39	64
    6	LCK-05	Smart lock	Locks	229	8
    7	LCK-06	Keypad lock	Locks	119	22

    • ASmart lock
    • B5
    • CLCK-05
    • D6
    显示答案

    答案: B. 5

    MATCH returns the position of the item in the range, not the value. LCK-05 is the 5th cell in A2:A7 (it sits in row 6 of the sheet).

11. 第11题，共15题

    In the price list below, what does =INDEX(D2:D7,3) return?

    	A	B	C	D	E
    1	Code	Item	Category	Price	Stock
    2	CAM-01	Dome camera	Cameras	189.99	14
    3	CAM-02	Bullet camera	Cameras	149.5	0
    4	ALM-10	Door sensor	Alarms	24.75	120
    5	ALM-11	Motion sensor	Alarms	39	64
    6	LCK-05	Smart lock	Locks	229	8
    7	LCK-06	Keypad lock	Locks	119	22

    • AALM-10
    • B24.75
    • C149.5
    • D39
    显示答案

    答案: B. 24.75

    INDEX returns the value at a position in a range. The 3rd cell of D2:D7 is D4, which holds 24.75.

12. 第12题，共15题

    In the price list below, what does =INDEX(B2:B7,MATCH(229,D2:D7,0)) return?

    	A	B	C	D	E
    1	Code	Item	Category	Price	Stock
    2	CAM-01	Dome camera	Cameras	189.99	14
    3	CAM-02	Bullet camera	Cameras	149.5	0
    4	ALM-10	Door sensor	Alarms	24.75	120
    5	ALM-11	Motion sensor	Alarms	39	64
    6	LCK-05	Smart lock	Locks	229	8
    7	LCK-06	Keypad lock	Locks	119	22

    • AKeypad lock
    • BSmart lock
    • C5
    • DLCK-05
    显示答案

    答案: B. Smart lock

    MATCH finds 229 at position 5 of D2:D7. INDEX then returns the 5th item of B2:B7, Smart lock.

13. 第13题，共15题

    In the price list below, what does =IFERROR(VLOOKUP("ALM-12",A2:E7,4,FALSE),0) return?

    	A	B	C	D	E
    1	Code	Item	Category	Price	Stock
    2	CAM-01	Dome camera	Cameras	189.99	14
    3	CAM-02	Bullet camera	Cameras	149.5	0
    4	ALM-10	Door sensor	Alarms	24.75	120
    5	ALM-11	Motion sensor	Alarms	39	64
    6	LCK-05	Smart lock	Locks	229	8
    7	LCK-06	Keypad lock	Locks	119	22

    • A#N/A
    • BALM-12
    • C0
    • D39
    显示答案

    答案: C. 0

    ALM-12 is not in the list, so VLOOKUP gives #N/A. IFERROR catches the error and returns 0 instead.

14. 第14题，共15题

    You want to replace only the #N/A error from a lookup with "Not found", but still see other errors such as #REF!. Which function fits best?

    • AIFERROR
    • BISBLANK
    • CIFNA
    • DIFS
    显示答案

    答案: C. IFNA

    IFNA only replaces #N/A. IFERROR also hides #REF!, #VALUE!, #DIV/0! and others, which can hide real mistakes.

15. 第15题，共15题

    In the commission table below, what does =XLOOKUP(32000,A2:A5,B2:B5,,-1) return?

    	A	B
    1	Sales from	Rate
    2	0	2%
    3	10000	3%
    4	25000	4%
    5	50000	5%

    • A4%
    • B5%
    • C3%
    • D#N/A
    显示答案

    答案: A. 4%

    match_mode -1 means "exact match, or else the next smaller item". 32,000 is not listed, so XLOOKUP uses 25,000 and returns 4%.

键盘：按1–4作答，按 Enter 进入下一题。