Hey Guys,


I've been stuck on this problem, It has real world application. As a simplification:
I have a fixed table with:


a | Gold
b | Silver
b | Aluminium
c | Tin
c | Copper
c | Hydrogen
d | Helium
e | Lithium

Now I have a different table, where I need an excel function to input and fill out this table based on the criteria of the table above.

[Column] [Column] [Column]
[First Match] [Second Match] [Third Match]
a
b
c
d
e

I need a function that would populate the table like so:

[Column] [Column] [Column]
[First Match] [Second Match] [Third Match]
a Gold N/A N/A
b Silver Aluminium N/A
c Tin Copper Hydrogen
d Helium N/A N/A
e Lithium N/A N/A

So far I can get the first column to work, but I can't get a function that will produce a second and third match. So far the most progress I have made has been for the first column which works with a =index match function. I have attached an excel document with my progress of this.

Any help will be most appreciated! Thanks for reading!

Cheers,

Best Regards

Attached Files

Index Match Problem.xlsx (8.8 KB, 17 views)