Return Multiple Matches

Question

Hi, I'm trying to get Excel to return multiple values from any match that it encounters without it spilling and every formula I've found from Google has spilled since it expects me to have free cells, which I don't. I need them to match 1:1.

Here's a screenshot that can probably explain much better than I can using words.

The Workbook on the right received the data in the A column and they are not necessarily unique nor might they be the same exact amount as in the Workbook on the left and not in the same order either. I'm trying to cross-reference the Workbook on the left by searching for the matching number and returning the amount that corresponds to that match.

Answer 1

Hi,

Try this formula in cell E1 and copy down

=IFERROR(INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4=$D1,ROW($A$1:$A$4)),COUNTIF(D$1:D1,D1)),1),"")

Hope this helps.

Answer 2

After looking through each function using F9 for several different cells, I understand now. Thank you very much again!

Answer 3

You are welcome. Thank you for your kind words. May i request you to read up on the INDEX(), SMALL() and COUNTIF() functions. Once you are clear with what these functions do, try to view the result of these functions in the formula by highlighting the specific function in the formula and pressing F9. If you still do not understand what the formula does, post back.

Answer 4

Yes! That worked! Thank you very much. I really appreciate it.

If it's possible, could you explain this formula to me? Like dissected. I am really interested, and I feel the best way to learn is to know how something works, not by just using it.

If you are busy or don't want to, no worries. I completely understand and am very grateful regardless.

Thank you again! :)