A teacher has created a spreadsheet of test marks of five students. She has…
2022
A teacher has created a spreadsheet of test marks of five students. She has written a formula =HLOOKUP(B4, $D$1:$K$2, 2) in cell C4 and copies this formula from cell C4 to the range C5:C8. What values will be returned by the formulae in the range C4:C8?
A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 0 | 9 | 14 | 19 | 27 | 33 | 40 | 47 | |||
2 | U | G | F | E | D | C | B | A | |||
3 | Name | Marks | |||||||||
4 | Anil | 43 | =HLOOKUP(B4, $D$1:$K$2, 2) | ||||||||
5 | Amit | 85 | |||||||||
6 | Varun | 35 | |||||||||
7 | Anita | 20 | |||||||||
8 | Harish | 10 |
Answer: D. B, A, C, E and G, respectively — ConceptWhen HLOOKUP uses approximate matching, it searches the first row of a table arranged in ascending order and selects the greatest lookup value that is…
- A.
A, C, E, G and F, respectively
- B.
B, C, E, G and U, respectively
- C.
A, C, E, F and U, respectively
- D.
B, A, C, E and G, respectively
Show answer & explanation
Correct answer: D
Concept
When HLOOKUP uses approximate matching, it searches the first row of a table arranged in ascending order and selects the greatest lookup value that is less than or equal to the search value.
The row-index argument then returns the entry from that same column in the specified row. If the fourth argument is omitted, approximate matching is used.
Application
In C4, B4 contains 43. The greatest first-row value not exceeding 43 is 40, whose second-row entry is B.
After copying to C5, the lookup reference becomes B5 = 85, while $D$1:$K$2 stays fixed. Since 85 exceeds the largest breakpoint 47, the selected second-row entry is A.
In C6, B6 = 35. The greatest breakpoint not exceeding 35 is 33, so the returned entry is C.
In C7, B7 = 20. The greatest breakpoint not exceeding 20 is 19, so the returned entry is E.
In C8, B8 = 10. The greatest breakpoint not exceeding 10 is 9, so the returned entry is G.
Cross-check
The lookup values B4:B8 change as 43, 85, 35, 20, and 10 because B4 is relative.
The table array does not move because $D$1:$K$2 is absolute, and its first row is already in ascending order.
Result
Therefore, C4:C8 returns B, A, C, E, and G, respectively.