VLOOKUP only returns the first matching result. If there are multiple matches, can VLOOKUP handle that?
{=INDEX (B:B,SMALL (IF ($B$10:$B$16=”A”,ROW ($10:$16),2^20),ROW (1:1)))&””}
- This is an array formula (enter the formula and press Ctrl+Shift+Enter).
IF($B$10:$B$16="A",ROW($10:$16),2^20)
- If B10:B16 equals A, return the corresponding number; otherwise return 2^20=1048576, which is Excel's maximum number of rows.
- For example, if B10 is A, return 10; if B11 is B (not A), return 1048576.
- So the current array of numbers is {10;1048576;12;1048576;1048576;15;1048576}.
SMALL(IF($B$10:$B$16="A",ROW($10:$16),2^20),ROW(1:1))
- SMALL syntax returns the Nth smallest number in a range. =SMALL(range, N)
- ROW(1:1) returns 1; dragging the formula down becomes ROW(2:2), which returns 2.
- The full formula finds the 1st smallest number in {10;1048576;12;1048576;1048576;15;1048576}, which is 10. If you drag the formula down, ROW(1:1) becomes ROW(2:2), so it finds the 2nd smallest number, which is 12.
INDEX(B:B,SMALL(IF($B$10:$B$16="A",ROW($10:$16),2^20),ROW(1:1)))
- INDEX returns the Nth item from a range. =INDEX(range, N)
- Because SMALL() returns 10, INDEX returns "A". Dragging down will also return the second "A".
- If the formula is in F10 it returns "John"; F11 returns "Tim".
&””
- Purpose of hiding zero values
