How to Use VLOOKUP to Return All Matches in Google Sheets

To return all matches using VLOOKUP in Google Sheets, you will need to use an array formula, which combines VLOOKUP with other functions like IFERROR and ROW. In this example, let's say you have a dataset in the range A1:B10, and you want to find all matches for the value in cell D1.

Example:

1. Prepare your Google Sheet with the dataset in the range A1:B10.
2. In cell D1, type the value you want to search for.
3. In cell E1, type the following formula:

=ArrayFormula(IFERROR(VLOOKUP(D1, A1:B10, 2, FALSE), ""))

4. Press Enter to apply the formula.

This formula will return the first match for the value in D1. To return all matches, you will need to use an array formula with a combination of functions.

5. In cell F1, type the following formula:

=ArrayFormula(IFERROR(VLOOKUP(FILTER(A1:A10, IFERROR(VLOOKUP(A1:A10, A1:B10, 2, FALSE)) = D1), A1:B10, 2, FALSE), ""))

6. Press Enter to apply the formula.

This formula will return all matches for the value in D1 in the range F1:F10. The FILTER function filters the range A1:A10 based on the condition that the VLOOKUP result equals the value in D1. The IFERROR function is used to handle any errors in the VLOOKUP results.

Now, you can use this formula to return all matches using VLOOKUP in Google Sheets. Just make sure to adjust the range and column index according to your dataset.