SOLVED

# Excel: Lookup '1' and return multiple values

Copper Contributor

# 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.

best response confirmed by pycharge (Copper Contributor)
Solution

# Re: Excel: Lookup '1' and return multiple values

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.

1 best response

Accepted Solutions
best response confirmed by pycharge (Copper Contributor)
Solution

# Re: Excel: Lookup '1' and return multiple values

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.