Lookup challenge, which of the 6 values is found in a different single column

[scottlarock]

Hi everyone,

I have 6 columns, side by side, with a product family in each, and this data is listed down a range of rows.
I need to see if one of the 6 possible product families alongside each row is found within a different single column on another sheet (there may be more than one TRUE statements but returning the first matching product Family is sufficient in that case).

I was able to manage this but I had to break the formula down into 7 parts, which weighs a ton in my already very heavy workbook.

Basically I added helper columns (from H to M) where each column represents 1 of the 6 possible product families (from A6:A10000 to F6:F10000), in order to return a TRUE if one of the product families was found in the different single column

Formula in H6=IF(ISERROR(MATCH(A6,All_Product_Families,0)),FALSE,TRUE))
Formula in I6=IF(ISERROR(MATCH(B6,All_Product_Families,0)),FALSE,TRUE))
Formula in J6=IF(ISERROR(MATCH(C6,All_Product_Families,0)),FALSE,TRUE))
Formula in K6=IF(ISERROR(MATCH(D6,All_Product_Families,0)),FALSE,TRUE))
Formula in L6=IF(ISERROR(MATCH(E6,All_Product_Families,0)),FALSE,TRUE))
Formula in M6=IF(ISERROR(MATCH(F6,All_Product_Families,0)),FALSE,TRUE))

And then I add the 7th helper column to return the product family number listed in H1:M1 (result would be from 1 to 6), where A1=H1=1; B1=I1=2; C1=J1=3; D1=K1=4; E1=L1=5; F1=M1=6

Formula in G6=INDEX($H$1:$M$1,MATCH(TRUE,H6:M6,0))

This adds 6 significant lookups + the 7th easy lookup... but I NEED to simplify this into a single lookup otherwise nobody will be able to use this file as it is too darn heavy...

Tons of thanks in advance for your strong support !

 

[sheetspread]

This example counts the matches in a row to another range, it's even easier if only the headers vary and not the records:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Name	Name1	Name2	Name3	Name4	Name5				EEEEE
2	A	AA	AAA	AAAA	AAAAA	AAAAAA	0			BBBBBB
3	B	BB	BBB	BBBB	BBBBB	BBBBBB	1			FFFFFF
4	C	CC	CCC	CCCC	CCCCC	CCCCCC	0
5	D	DD	DDD	DDDD	DDDDD	DDDDDD	0
6	E	EE	EEE	EEEE	EEEEE	EEEEEE	1
7	F	FF	FFF	FFFF	FFFFF	FFFFFF	1
8	G	GG	GGG	GGGG	GGGGG	GGGGGG	0
Sheet2

Cell Formulas
Range	Formula
G2	{=SUM(COUNTIF($A2:$F2,$J$1:$J$3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[scottlarock]

Thank you very much, yet what I need is for the formula to return either the 1st matching product family name itself, or the product fam number (1 to 6).

 

[barry houdini]

Try this "array formula"

=INDEX($H$1:$M$1,MATCH(TRUE,ISNUMBER(MATCH(A6:F6,All_Product_Families,0)),0))

confirmed with CTRL+SHIFT+ENTER

although if the values in H1:M1 are always 1,2,3,4,5 and 6 the MATCH alone would be sufficient, i.e.

=MATCH(TRUE,ISNUMBER(MATCH(A6:F6,All_Product_Families,0)),0)

 

[scottlarock]

Amazing !
Thank you so much Barry !!
I used the Match only formula, it is perfectissimo :°)) I guess it is the lightest and works like a charm too.
Have a great Sunday !