Excel - Return multiple matching values from a column matching two variables, horizontally in one row

1
votes

I have this table:

|   | A       | B      | C |
|---|---------|--------|---|
| 1 |         |        |   |
| 2 | Oranges | Red    | 1 |
| 3 | Apples  | Yellow | 2 |
| 4 | Grapes  | Orange | 3 |
| 5 | Oranges | Orange | 4 |
| 6 | Apples  | Red    | 5 |
| 7 | Grapes  | Green  | 6 |
| 8 | Apples  | Green  | 7 |

I want to check for matching values in Column A like Apples,Yellow , Apples,Green, etc... and return all the corresponding values from Column B in one row:

I tried to nest AND into IF but didn't work out as it wasn't returning any values at all.

|    | A       | B           | C | D | E |
|----|---------|-------------|---|---|---|
| 11 | Apples  | Green       | 1 |   |   |
| 12 | Oranges | YellowGreen | 2 |   |   |

My code:

=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))

How do I get this formula to look at two variables to match?

Thank you.

1
excelindexingmatchlookup

1 Answers

1
votes

You seem to be using an array formula, wouldn't concatenating work?

{=INDEX($C$2:$C$8, SMALL(IF($A11&" "&$B11=$A$2:$A$8&" "&$B$2:$B$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))}