Returning more than one value to the same cell.

[rickblunt]

Hello, I need to scan a column of numbers for a target value and then pass the values from the column to the left of the found targets to a cell. I normally do this with a list box in a userform but this time I need to do it in a worksheet cell instead.

I am scanning column B for the values. (numbers).
I want to return the found values from column A (text).
The number in "H4" is my target value.

I can use this formula to find the first occurrence, but how do I change the formula to get multiples to show up in the same cell? I do not know if I use the count function or the index or something like that.

For this example I have the formula in C1

=IF(B19<=H4,A19,"")

I appreciate any thoughts - thanks

 

[Fluff]

As long as col B is real numbers & not text ("45" is text & not a number) the formula I supplied should work. You also need to leave the "" as is, or you will get None returned for every row which does not fit the criteria.

 

[rickblunt]

Did you try Fluff's suggestion?
It looks like he came with an array formula that will do what you want.

bam - bam - bam (the sound of my head hitting my desk) Thank you to both of you for the solution, Fluff for the formula and Joe4 for pointing out that its an array formula, which revealed that I was trying to put this in a merged cell which is also not allowed... Many thanks once again to both of you for another lesson...

 

[Fluff]

Glad we could help & thanks for the feedback.