Hi,
I a have a matrix like table as show in the image below:
ibju30XaMmrXNh.jpg
I need a formula in cell B18, C18, D18 etc.. that will list all cell values from colum A if in column B (or C, D etc.. depending of the formula) there is a certain value.
For example in range B2:B17 we have the value "2" in three cells. The formula should display "B2,B4,B14" as values from range A2:A17 that have in range B2:B17 the value 2.
The formula should be something like this (I know it's not even close the be correct):
=IF("2" is in range B2:B17; "B2,B4,B14"; "-")
This formula must be used for the rest of the columns C, D, E...
=IF("2" is in C2:C17; "B1,B8"; "-") etc...
So if a value.. let's say 2 is in range B2:B17, display "B2,B4,B14" (here B2,B4,B14 are random cell values not column names.
I have no ideea what functions to use, and I don't even know if it's possible... (I cant use MATCH for the "value is in range" thing because if there is no such value in range it will return #N/A error. I cant use vlookup for the display of values because vlookup doesn't work when the search item is not the first argument in the range...)
I'm sorry if my post is not very clear, and I would be happy to answer any questions that will you help me
Thank you for your help!
Bookmarks