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

1. Question 1 sur 15

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

   • ANext larger item
   • BWildcard match
   • CApproximate match
   • DExact match
   Voir la réponse

   Réponse: D. Exact match

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

2. Question 2 sur 15

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

   	A	B	C	D	E
   1	Code	Élément	Catégorie	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
   Voir la réponse

   Réponse: D. 39

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

3. Question 3 sur 15

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

   	A	B	C	D	E
   1	Code	Élément	Catégorie	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
   Voir la réponse

   Réponse: 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. Question 4 sur 15

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

   	A	B	C	D	E
   1	Code	Élément	Catégorie	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
   Voir la réponse

   Réponse: 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. Question 5 sur 15

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

   	A	B	C	D	E
   1	Code	Élément	Catégorie	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
   Voir la réponse

   Réponse: 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. Question 6 sur 15

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

   	A	B	C	D	E
   1	Code	Élément	Catégorie	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!
   Voir la réponse

   Réponse: 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. Question 7 sur 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
   Voir la réponse

   Réponse: 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. Question 8 sur 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	Élément	Catégorie	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
   Voir la réponse

   Réponse: D. Not listed

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

9. Question 9 sur 15

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

   	A	B	C	D	E
   1	Code	Élément	Catégorie	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
   Voir la réponse

   Réponse: C. #N/A

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

10. Question 10 sur 15

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

    	A	B	C	D	E
    1	Code	Élément	Catégorie	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
    Voir la réponse

    Réponse: 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. Question 11 sur 15

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

    	A	B	C	D	E
    1	Code	Élément	Catégorie	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
    Voir la réponse

    Réponse: 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. Question 12 sur 15

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

    	A	B	C	D	E
    1	Code	Élément	Catégorie	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
    Voir la réponse

    Réponse: B. Smart lock

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

13. Question 13 sur 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	Élément	Catégorie	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
    Voir la réponse

    Réponse: C. 0

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

14. Question 14 sur 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
    Voir la réponse

    Réponse: C. IFNA

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

15. Question 15 sur 15

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

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

    • A4%
    • B5%
    • C3%
    • D#N/A
    Voir la réponse

    Réponse: 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%.

Clavier : 1–4 pour répondre, Entrée pour la question suivante.