How to get multiple results with the VLOOKUP function.(Microsoft Excel)
The VLOOKUP function retrieves data from a single row that matches the search criteria.
Since it only retrieves the first (top) cell, it is not appropriate when there is more than one cell that corresponds to the search condition.
Therefore, we will show you how to apply the VLOOKUP function to multiple results.
In some cases, another method may be more efficient, so we will introduce that method as well.
Steps
Take the following table as an example.
VLOOKUP function.
The steps are the same for the XLOOKUP function.
Prepare cells with sequential numbers.
Use the COUNTIF function to count up within the same “Category”.
=COUNTIF($E$3:E3,E3)
MAX function gets the maximum value.
Concatenate "Category" and sequential numbers with "&".
Enter one “Category” and all applicable data will be displayed.
Create a search sheet as follows. The “sequential number” on the far left is a fixed number.
You need to prepare from 1 to the maximum value of the sequential number.
String concatenation is used to create a search key.
The “Category” should be an absolute reference to prevent formula deviations.
Set the formula for the VLOOKUP function (XLOOKUP function).
If the number present is less than the maximum, a #N/A error will occur.
If necessary, use the IFERROR function to deal with this problem.
Alternate method. Filter.
If you simply want to find them, using a filter is most appropriate.
Filters show or hide cells.
It is suitable for applications where you frequently change the conditions of your search.
Functions are better if you do not want to manipulate the table or if you want to incorporate the search into the sheet.
Alternate method. FILTER function.
FILTER function is available for Office365 and Excel 2019 or later.
It is very useful if available.
The formula should be as follows.
=FILTER(List to search{Not including header rows},Column to Search=Search value)
In this example, the following will be used. (Intermediate cells are deleted)
=FILTER(B3:C13,B3:B13=F2)
If you set this in one cell (F6 in the example), it will automatically expand by the required amount in a spill.
This function is very useful because it automatically supports multi-column display.
If you set it to one cell (E3 in the example), it will automatically expand by the required amount in a Spill.
---