Find a value in a table

[CFI_Pigman]

I've done this before, but it is amazing how you can draw a blank and also not find the file where the formula was used years earlier...I have a sheet where I would like to update a bin list in column J and what is in the bin in column K. Columns A, C and E would be the table where I would apply the bin # to the product. There are multiple columns so I can then calculate volumes.

I simply wish to have column K update any time I change the value in A, C or E...using a formula, not VBA

	a	b	c	d	e	f	g	h	i	j	k
1	Bin	Product	Bin	Product	Bin	Product				Bin List	Product
2	P4	Corn	P6	Corn						P1
3	P3	Wheat								P2
4										P3	Wheat
5										P4	Corn
6										P5
7										P6	Corn
8										P7
9										P8
10										P9
11										P10

<tbody>
</tbody>

Thanks in advance.

 

[Aladin Akyurek]

If you have the TEXTJOIN function, in K2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF("|"&$A$2:$E$11&"|"="|"&J2&"|",IF(1-($B$2:$F$11=""),$B$2:$F$11,""),""))

 

[CFI_Pigman]

Thank you for reply...My apologies I should mention Excel 2010...I do not have the new TextJoin function

Regards

 

[Aladin Akyurek]

Thank you for reply...My apologies I should mention Excel 2010...I do not have the new TextJoin function

Regards

Are the combinations of bin and product occurrences unique?

 

[CFI_Pigman]

yes...I have 50+ bins with a unique identification. Each bin can never have 2 products in it, but I could have 4 different bins filled with the same product for storage. I don't show it but the products are listed in each row from A2 down and I keep the same product in its specific row...the Bin List is located on the same page. My goal is to input the bin # to identify that I have added product to it or changed its location...then the bin list will automatically update with what product is now in that bin. In the past I've used Sumproduct with Match and Index...but I'm not having success.

 

[Aladin Akyurek]

In K2 control+shift+enter, not just enter, and copy down:

=IF(COUNTIF($A$2:$F$11,J2),INDEX($A$2:$F$11,MIN(IF($A$2:$F$11=J2,ROW($A$2:$F$11)-ROW($A$2)+1)),1+MIN(IF($A$2:$F$11=J2,COLUMN($A$2:$F$11)-COLUMN($A$2)+1))),"")

 

[CFI_Pigman]

Brilliant.

Thanks

 

[Aladin Akyurek]

Brilliant.

Thanks

Glad to help.