Lookup return multiple values excel

Hem / Teknik & Digitalt / Lookup return multiple values excel

If you only need to get one matching value, XLOOKUP, TEXTJOIN and IF functions can handle it well.

Each method works best in different situations. In these cases, Excel’s lookup functions can help you return multiple values quickly.

There are several ways to look up multiple values using Excel formulas.

In that case, you can nest one FILTER function inside another to pull only the columns you need.

For example, to return the name from column B and the registration date from column D, omitting the country in columns C, the formula is:

=FILTER(FILTER(B3:D25, A3:A25=F3), {1,0,1})

How this formula works:

  1. The inner FILTER retrieves all rows from columns B through D where the event in column A matches the value in F3.

    The result still includes three columns: Participant Name (B), Country (C), and Registration Date (D).

  2. The outer FILTER with a column selector doesn't filter rows, it filters columns. For example, type Laptop in cell H2, Headphones in H3, Monitor in H4, and Webcam in H5.

    ➤ Click on cell G2 and enter this formula

    ➤ Press Enter.

    Learn More →

Looking up multiple values in Excel might sound tricky, but it’s actually a breeze once you get the hang of it. This will allow you to efficiently retrieve data based on multiple criteria.

Step 1: Set Up Your Data

First things first, ensure your data is neatly arranged in a table or list format.

It’s crucial to have your data organized in columns and rows with clear headers.

To get this done, nest REGEXTEST inside the include argument of FILTER like this:

=FILTER(A3:C25, REGEXTEST(A3:A25, E3))

This formula returns all rows where the ID contains the text specified in E3, no matter where it appears in the string.

If you need something more precise, you can use various regex patterns to control exactly what qualifies as a match.

Match entries that start with certain characters

To return only those entries where the ID begins with the code in E3, use the caret ^ anchor, which tells Excel to match the pattern only at the very start of the string.

=FILTER(A3:C25, REGEXTEST(A3:A25, "^" & E3))

Match entries that end with certain characters

To pull all the records where ID ends with a country code typed in E13, use the regex end anchor $:

=FILTER(A3:C25, REGEXTEST(A3:A25, E13 & "$"))

How these formulas work:

  • REGEXTEST checks each ID in A3:A25 and returns TRUE for rows that match the pattern and FALSE for those that don't.
  • FILTER keeps only the rows where the regex condition is TRUE.
  • The output is a full list of rows that satisfy your partial-match condition.

This approach is flexible, powerful, and ideal for situations where simple wildcards just aren't enough.

Now that you've seen how to return multiple matches in many different ways, from simple filters to multi-criteria lookups and regex searches, you can choose the method that fits your worksheet best.

lookup return multiple values excel

The horizontal array constant in the second argument acts as a column mask, where 1 (TRUE) means "keep this column" and 0 (FALSE) means "skip this column".

In our case, the array {1,0,1} tells the outer function to return the first and third columns from the inner result (B and D) and excludes the middle one (C).

The final output is a dynamic array showing only the participant names and their registration dates for the selected event.

For more information, see how to filter specific columns in Excel with formulas.

How to find all matches with multiple criteria in Excel

At times, you might need to match several columns at once.

That means you won’t be able to use the result directly in calculations unless you split and convert it back into numbers.

3

Use XLOOKUP with Multiple Lookup Values

The XLOOKUP function is one of Excel’s most flexible lookup tools. In the case of “Orange” also, it provides 56 for all the cities.

So, in these cases, we need to create a “Helper” column to create a unique lookup value list.

This helps keep your lookup operations separate from your main data.

Step 3: Identify the Criteria

Step 3 is to determine the criteria you will use for your lookup.

Knowing exactly what you need to search for (e.g., names, dates, product codes) is important. The second argument of the FILTER formula that defines the criteria includes two logical expressions:

  • A3:A25=F4 checks each cell in column A against the event name in F4.
  • C3:C25=G4 checks each cell in column C against the country name in G4.

Each comparison produces an array of Boolean values, where TRUE equates to 1 and FALSE to 0.

The same has been returned for all the cities. When you have multiple criteria, the FILTER function can handle it smoothly.

In this method, we’ll look up values based on both Customer Name and Product. If you’re using an older version of Excel, try the next method which works with array formulas.

2

Combine TEXTJOIN with IF to Return Multiple Values in One Cell

If you want to look up multiple matching values and display them in a single cell, you can use the TEXTJOIN function combined with an IF statement.

In our case, the second argument checks your lookup value against each entry in the lookup array. FILTER then returns all values from column D that match the name in F2.
This formula works dynamically. Learn how to efficiently use VLOOKUP, HLOOKUP, INDEX-MATCH, XLOOKUP, and more to find and manage data with precision.

First, we extract all matching values from column A with a standard FILTER formula:

FILTER($B$3:$B$25, $A$3:$A$25=D3, "")

This creates a dynamic array of all participant names related to the event in D3.

  • Combine the results. This makes it easier for Excel to locate and retrieve the required values.

    Step 2: Open a New Worksheet

    Step 2 involves opening a new worksheet where you will perform your lookup.

    You can either open a new worksheet in your existing workbook or create a new workbook altogether.

    It works only in Excel 365 or Excel 2021 and is perfect when you want to extract several values that meet the same condition.

    In this method, we’ll pull all the order amounts placed by a single customer from our dataset.

    Let’s find all orders made by Alice.

    Here’s how to do it:

    ➤ Open your dataset in Excel.
    ➤ Click on cell G2, enter the following formula

    ➤ Press Enter.

    We’ll retrieve the most recent order for four customers: Alice, Bob, David and Charlie.

    Here’s how to do it:

    ➤ Open your dataset in Excel and select the column where you want to display the latest order amount of Alice.
    ➤ In cell G2, enter the following formula

    ➤ Press Enter, then drag the fill handle down to apply the formula to the rest of the rows.

    ➤ Next, drag the fill handle down to copy the formula to the rest of the rows for Bob, David, and Charlie.

    Note:
    This method works best when each lookup value has one latest record or when you only need one result per name.

    These lookup techniques will help you manage and analyze your data more efficiently in Excel.

    Post Views:505

    • How to lookup and return multiple matches in Excel

    This tutorial shows how to extract all matching entries in Excel using dynamic array functions, multi-criteria lookups, and regex searches, with practical examples and formula breakdown.

    Finding a single match in Excel is easy with VLOOKUP or XLOOKUP.