possible index match formula?

[banneduser123]

In have two worksheets. In Worksheet 1, I have one column. In Worksheet 2, I have two columns.


Worksheet 1, has a list of colors
Worksheet 2, also has a list of colors in column A, and in column B, it has a number indicated next to each color.


How can I create a formula in Worksheet 1, that will lookup the value in column A, and return all the different inputs associated with that value in Worksheet 2?


Worksheet 1, Column A

Purple
Orange
Blue



Worksheet 2, Column A Worksheet 2, Column B

Purple 8
Blue 3
Blue 54
Orange 6


So what I'm looking for is a formula, that would look at the color in Worksheet 1, (column A, row 3) and look it up in Worksheet two, and then return "3,54" etc....


thanks in advance

 

[Legacy 436357]

Maybe like this?

Excel Workbook
	A	B
1	Color	Number
2	Purple	5
3	Blue	7
4	Green	9
5	Yellow	3
Sheet1

 

[Eric W]

If you have the TEXTJOIN function (Excel 365), you can use this formula in B2:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
=TEXTJOIN(", ",TRUE,IF(Sheet2!$A$2:$A$10=A2,Sheet2!$B$2:$B$10,""))

confirmed with Control+Shift+Enter.

If you don't have TEXTJOIN, we can come up with a formula that puts the first match in column B, the next in column C, etc. If you want them in a single cell, you'd need either a lot of helper cells, or a VBA macro. Hope this helps.

 

[banneduser123]

thanks Eric.
I don't have TEXTJOIN

I just put together this array, which I think accomplishes what I'm looking for, even though I'd have to throw the formula into many additional columns to make sure I pick up all the possible values.....the problem with this, is that it won't account for duplicates...do you know how to incorporate this?

to articulate against the example I used earlier, I wouldn't want to formula to continually return the value "34" every time it appears against the color "blue"..i would only want it returned goes on to pick up the next unique value....was that clear?

=INDEX(Sheet!$C:$C,SMALL(IF('Export Worksheet'!F2=Sheet!$A:$A,ROW(Sheet!$A:$A)-MIN(ROW(Sheet!$A:$A))+1,""),1))

 

[banneduser123]

eh nevermind...I just removed duplicates....the formula I made seems to do the job fine...thanks for the help guys

 

[banneduser123]

Actually. I’m having an issue with the formula I came up with....

Towards the end of the formula, it has the “+8”, telling it to return the 8th respective value.
Generally if there are no more values, it should just return the first value that was found for the case. But I noticed after a certain amount of times, it’ll just pull in random values.

Do you know why it does this?

 

[banneduser123]

Oh. I wasn’t putting in the right input for k. Nevermind