INDEX/MATCH? - Return Value to left with IF function

[macrav]

I'm just starting to wrap my head around the INDEX/MATCH function but can't quite get it, especially when trying to include an IF function. Not entire sure what I'm looking to do can be done so I'm challenging the experts.

I'm looking to run a formula that says, "if you find a "Yes", return anything to the left of it". Using the example below, the hope is to return a list of Red, Green, and Orange.

The below will give me "Red" but I'm struggling with how to relate this to the rest of the list. When I drag it down, I only get "Red" on the other "Yes" responses.

=IF(B1="Yes",INDEX($A$1:$A$6,MATCH(B1,$B$1:$B$6,0)))

A B
1 Red Yes
2 Blue No
3 Green Yes
4 Purple No
5 Yellow No
6 Orange Yes


Thanks in advance for your help!

[ben_hensel]

what about filtering column B for "Yes", would that fill your need?

[ConneXionLost]

If you're looking for a formula to drag down, then all you need is:

Please Login or Register  to view this content.

Cheers,

[Ron Coderre]

If you're trying to get cell C1 to return the concatenated list of Col_A values where the Col_B cell contains Yes (Red, Green, Orange)...Excel does an exceptionally poor job of concatentating matching items. Either the formula quickly becomes too unwieldy to work with or you end up needing VBA to sort out the values. Consequently, I'd recommend another approach.

[Tony Valko]

Is this what you're wanting to do?

Data Range

	A	B	C	D
1	Red	Yes		Red
2	Blue	No		Green
3	Green	Yes		Orange
4	Purple	No
5	Yellow	No
6	Orange	Yes
7	------	------	------	------

This array formula** entered in D1:

=IFERROR(INDEX(A:A,SMALL(IF(B$1:B$6="Yes",ROW(B$1:B$6)),ROWS(D$1:D1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[macrav]

Thanks to everyone who responded. Because I'm pulling from one sheet to another, filtering won't work. A simple IF function doesn't provide the results in the format I'm looking for.

That said, special and HUGE THANK YOU to Tony Valko who NAILED IT. Exactly what I was looking for!!

[macrav]

Thanks so much, Tony. EXACTLY what I was looking for!

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.