Lookup To Return Multiple Results — Using JOIN, COLLECT

Hammermill21

Hello,

I am trying to use JOIN, COLLECT to return multiple results but just can't seem to get it to work.

This is what I have:

=JOIN(COLLECT([Column111]:[Column111], [Column7]:[Column7], [Column1]@row, "N/A"))

[Column111]:[Column111] is the range of the data I want to be returned

[Column7]:[Column7] is the range of where to look for the data

[Column1]@row is the cell that contains the data I want to find within the range

N/A is the result I am looking for if there are no matches

Any ideas?

Thank you!

Hammermill21

I figured it out! Thanks anyhow!

Antonio Figueroa

I think it would be easier to use vlookup

=vlookup(search_value,Lookup_Table, Collumn num, Match_Type)

and if you want to return an NA you can do

an if error

=iferror(vlookup(search_value,Lookup_Table, Collumn num, Match_Type),"N/A")

Hammermill21

Hi, thank you! But that only brings up one result though. I did an INDEX/MATCH which works but only brings up one result and I need multiple results.

Hammermill21

I figured it out! Thanks anyhow!