Forum Discussion

pycharge's avatar
pycharge
Copper Contributor
Apr 29, 2024
Solved

Excel: Lookup '1' and return multiple values

This seems rather simple be cannot currently find a solve for this.

I am currently in a spreadsheet, which has a column that is returning a binary value on the basis of random sampling.

I need to lookup the value '1', in this column and return all matching values from another 'item number' column in the same sheet.

I have tried XLOOKUP and Index Match, but they seem to just be returning the first value in the item number column, where I need each value returned in its own row.

Thanks in advance!

BI & Data Analysis
excel
Formulas and Functions
  • MAngosto's avatar
    MAngosto
    Apr 29, 2024

    Hello pycharge ,

     

    You can simply use the FILTER() function!

    Use FILTER(A1:A50,B1:B50=1)

    Where A1:A50 is the range of item numbers and B1:B50 is the range of the binary values.

3 Replies

Sort By
  • bb5931's avatar
    bb5931
    Copper Contributor
    Feb 19, 2025

    Hi MAngosto ,

    I have a similar question to the OP about return multiple matching values but I'm trying to find a function that counts how many cells contain a matching value (rather than return each of their individual values). Specifically I would like a function to count how many cells in B1:B20 contain "James" where corresponding value in A1:A20 equals "Yes".

    Please see attached screenshot to illustrate what I would like.

    Thanks for your help!

    • bb5931's avatar
      bb5931
      Copper Contributor
      Feb 20, 2025

      Found out! Just a simple COUNTIFS(A:A,"Yes",B:B,"James") !!

  • MAngosto's avatar
    MAngosto
    Iron Contributor
    Apr 29, 2024

    Hello pycharge ,

     

    You can simply use the FILTER() function!

    Use FILTER(A1:A50,B1:B50=1)

    Where A1:A50 is the range of item numbers and B1:B50 is the range of the binary values.

Resources