Formula to search range by two criteria or different two criteria, and if not found then search different range using same criteria(s)

[DrMrsStark]

Hello Excel Wizards! - Longtime reader, first time writer.

So I have a situation that I feel is completely do-able, because I've been able to successfully write one part of the formula to achieve my results, but cannot for the life of me get the entire formula right.
I apologize in advance for the XL mini-sheet samples.

All my data is separated into two .XLSM workbooks - One workbook with the "Data_Sample" table in it (the real file ranges from $A$2:$X$*is continuously growing in length as more data is added*), and one with the "Reference_Sample" table (the real file here ranges from $A$2:$DD$163 at this time, but more references might be added later).

I decided to separate the two sets of data into completely separate Workbooks because compiling all the data in one Workbook and on different Sheets was causing my device to run incredibly hot and Excel itself would freeze and crash, but also because I don't necessarily need the "Reference_Sample" table up or open when actively working on and adding data to the "Data_Sample" table.​

What I'm trying to do is in [RANK] (a.k.a. E2) of the "Data_Sample" table, search [BRAND] (a.k.a. G2) AND [COLOR(S)] (a.k.a. J2), OR search [BRAND] (a.k.a. G2) AND [COLOR(S) NAME] (a.k.a. S2) in the "References_Sample" table, and if there's a match then return the single value in the corresponding [COLOR #*** MATCH] (a.k.a. H2, L2, or P2) columns.

✱ But the trick(s) is the [BRAND] (a.k.a. G2) in the "Data_Sample" table absolutely has to match the [BRAND] (a.k.a. A2) in the "Reference_Sample" table, regardless what the other two criteria are.​

​

✱ Also the possible entries for [COLOR(S) NAME] (a.k.a. S2) of the "Data_Sample" table, and [COLOR #*** NAME] (a.k.a. G2, K2, or O2) in the "References_Sample" table tend to repeat as the Color Names for different Brands - This is also true for the possible entries for [COLOR(S)] (a.k.a. J2) in the "Data_Sample" table and [COLOR #*** NUMBER] (a.k.a. F2, J2, or N2) in the "References_Sample" table.​

✱ Ideally I would like for the formula to first search by [COLOR(S)] (a.k.a. J2) criteria, then if not found search the same set of data by the [COLOR(S) NAME] (a.k.a. S2) criteria... of course given the Brand is a match.​

✱ If possible I would like to avoid using helper columns, and my VBA experience is only YouTube deep.​

​

Also I've been mentally approaching the problem as "search Color #1 cell $E$2:$H$163 range for match, and if neither condition is met, then move on to search Color #2 cell $I$2:$L$163 range for match, and if neither condition is met, then continue on to search Color #3 cell $M$2:$P$163 range for match, etc. until the last Color #26 cell $DA$2:$DD$163 range" - But I'm starting to think this might be the source of my troubles!​

​

​

So far I've been able to successfully return values if the [BRAND] (a.k.a. G2) AND [COLOR(S)] (a.k.a. J2) has a match in the "References_Sample" table Color #1 cell range, but not with adding the the OR [BRAND] (a.k.a. G2) AND [COLOR(S) NAME] (a.k.a. S2) logic, or using variations of nested AND, OR, INDEX, MATCH, XLOOKUP, IFERROR, and OFFSET, such as:

1. nested IFERROR/INDEX/IFERROR/MATCH/OR/OFFSET/INDEX/IFERROR/MATCH/OR
2. nested XLOOKUP/OR/OFFSET/XLOOKUP
3. nested XLOOKUP/OR/XLOOKUP
4. nested XLOOKUP/XLOOKUP
5. nested XLOOKUP/XLOOKUP/OFFSET/OFFSET
6. nested XLOOKUP/IFERROR/XLOOKUP/OFFSET/IFERROR/OFFSET
7. nested IFERROR/INDEX/MATCH/OFFSET/XMATCH
8. nested XLOOKUP/OFFSET/OR/OFFSET
9. nested IFERROR/OR/XLOOKUP/XLOOKUP/OFFSET/OR/XLOOKUP/XLOOKUP
10. nested OR/XLOOKUP/XLOOKUP
11. nested XLOOKUP/OFFSET/XLOOKUP
12. nested XLOOKUP/OR/XLOOKUP/OFFSET/OR/OFFSET

WORKS:

=INDEX([Reference_Sample_Workbook.xlsm]Reference_Sample!$H$2:$H$163,MATCH(G2&J2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163,0))

NO BUENO:

=INDEX([Reference_Sample_Workbook.xlsm]Reference_Sample!$H$2:$H$163,OR(MATCH(G2&J2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163,0),MATCH(G2&S2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$G$2:$G$163,0)))

Again, to me what I'm trying to do seems feasible and not overly complicated, but I'm still learning when to use the appropriate functions and am really hoping that this is just a case of using the wrong function.
But nonetheless I welcome any and all suggestions and comments!!

-DrMrsStark


Data Table

Data_Sample-Workbook.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	RATING	PRO	MATCH	READY	RANK	NOTE(S)	BRAND	METHOD	COLOR TYPE	COLOR(S)	CONTAIN(S)	WIDTH	LENGTH	GRAMS	PRICE	PRICE/GRAM	PRO. PRICE	PRO. PRICE/GRAM	COLOR(S) NAME
2					1		Brand 3			12/613	1	50 to 60	22	220
3					#N/A		Brand 3			4/60	1	50 to 60	22	220
4					#N/A		Brand 6			P4/27	3		20	150
5					1		Brand 6			RP3/6/12	3		20	150
6					#N/A		Brand 7			4/22	3	33	22	63
7					2		Brand 7			4/12	3	33	22	63
8					#N/A		Brand 7			6/60	3	33	22	63
9					0		Brand 9			12/600	1	36	22.5	160					Color 12/Color 111
10					#N/A		Brand 9			4/613	1	36	22.5	160					Color 4/Color 10
11					#N/A		Brand 9			12/60	1	36	22.5	160					Color 12/Color 11
Data_Sample

Cell Formulas
Range	Formula
E2:E11	E2	=INDEX([Reference_Sample_Workbook.xlsm]Reference_Sample!$H$2:$H$163,MATCH(G2&J2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163,0))
L6:L8	L6	=11+11+11
L9:L11	L9	=36

Reference Table

Reference_Sample_Workbook.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	BRAND	ACCOUNT STATUS	RATING	COLOR REVIEW	COLOR #1 TYPE	COLOR #1 NUMBER	COLOR #1 NAME	COLOR #1 MATCH	COLOR #2 TYPE	COLOR #2 NUMBER	COLOR #2 NAME	COLOR #2 MATCH	COLOR #3 TYPE	COLOR #3 NUMBER	COLOR #3 NAME	COLOR #3 MATCH
2	Brand 1			-3
3	Brand 2			3		4	Color 4	2
4	Brand 3			3		12/613	Color 12/Color 10	1		4/60	Color 4/Color 11	2		6/60	Color 6/Color 11	2
5	Brand 4			-3
6	Brand 5			-3
7	Brand 6			3		RP3/6/12	Color 3/Color 12	1		RP6/18/613	Color 6/Color 10	0		P8/60	Color 8/Color 11	0
8	Brand 7			3		4/12		2		4/22		2		6/60		1
9	Brand 8			-3
10	Brand 9			3		12/600	Color 12/Color 111	0		12/60	Color 12/Color 11	1		1B/60	Color 1/Color 11	3
Reference_Sample

 

[jasonb75]

I haven't read through your entire post, just went straight to the 'almost' formula at the bottom and added 4 characters to it.

=INDEX([Reference_Sample_Workbook.xlsm]Reference_Sample!$H$2:$H$163,IFERROR(MATCH(G2&J2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163,0),MATCH(G2&S2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$G$2:$G$163,0)))

Another method for older versions of excel (2010 or newer). The 2 formulas below should be more efficient that using MATCH with concatenated arrays.

=INDEX([Reference_Sample_Workbook.xlsm]Reference_Sample!$H:$H,AGGREGATE(15,6,ROW([Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163)/([Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163=G2)/(([Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163=J2)+([Reference_Sample_Workbook.xlsm]Reference_Sample!$G$2:$G$163=S2)),1))

And one for office 365 (note that this will not work if the reference sample workbook is closed)

=FILTER([Reference_Sample_Workbook.xlsm]Reference_Sample!$H$2:$H$163([Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163=G2)*(([Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163=J2)+([Reference_Sample_Workbook.xlsm]Reference_Sample!$G$2:$G$163=S2)),"Not Found")

 

[DrMrsStark]

I haven't read through your entire post, just went straight to the 'almost' formula at the bottom and added 4 characters to it.

=INDEX([Reference_Sample_Workbook.xlsm]Reference_Sample!$H$2:$H$163,IFERROR(MATCH(G2&J2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163,0),MATCH(G2&S2,[Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163&[Reference_Sample_Workbook.xlsm]Reference_Sample!$G$2:$G$163,0)))

Another method for older versions of excel (2010 or newer). The 2 formulas below should be more efficient that using MATCH with concatenated arrays.

=INDEX([Reference_Sample_Workbook.xlsm]Reference_Sample!$H:$H,AGGREGATE(15,6,ROW([Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163)/([Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163=G2)/(([Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163=J2)+([Reference_Sample_Workbook.xlsm]Reference_Sample!$G$2:$G$163=S2)),1))

And one for office 365 (note that this will not work if the reference sample workbook is closed)

=FILTER([Reference_Sample_Workbook.xlsm]Reference_Sample!$H$2:$H$163([Reference_Sample_Workbook.xlsm]Reference_Sample!$A$2:$A$163=G2)*(([Reference_Sample_Workbook.xlsm]Reference_Sample!$F$2:$F$163=J2)+([Reference_Sample_Workbook.xlsm]Reference_Sample!$G$2:$G$163=S2)),"Not Found")

Hi jasonb75 and thank you for your reply! Unfortunately none of the formulas you provided solved the issue I'm having.

 

[jasonb75]

I missed part of what was required. It might be possible, but the reference sample is badly laid out. I would want to see the correct expected results in the mini sheet rather than just attempted formulas that don't work before attempting anything else.

 

[DrMrsStark]

I missed part of what was required. It might be possible, but the reference sample is badly laid out. I would want to see the correct expected results in the mini sheet rather than just attempted formulas that don't work before attempting anything else.

I absolutely agree, the reference sample isn't very friendly at all

I guess a better way to describe the goal is to match the brand in both sheets, then search columns in chunks (i.e. columns E:H, I:L, M:P, etc) and match either the color number or color name in each chunk, then return the value in the last column of the chunk searched.. so the actual return value will change based on which column had the match. For example, if the match was a color name, the return value would come from the column immediately to the right, and if the match was a color number, the return value would come from the column x1 column over to the right.

 

[jasonb75]

This is the only thing that I've been able to come up with that looks as if it is anywhere close. I've used the LET function to shorten the formula a little but it can be done without that if you don't have it yet.

Reference Data file.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	RANK	NOTE(S)	BRAND	METHOD	COLOR TYPE	COLOR(S)	CONTAIN(S)	WIDTH	LENGTH	GRAMS	PRICE	PRICE/GRAM	PRO. PRICE	PRO. PRICE/GRAM	COLOR(S) NAME
2	1		Brand 3			12/613	1	50 to 60	22	220
3	2		Brand 3			Apr-60	1	50 to 60	22	220
4	No match		Brand 6			P4/27	3		20	150
5	1		Brand 6			RP3/6/12	3		20	150
6	2		Brand 7			Apr-22	3	33	22	63
7	2		Brand 7			04-Dec	3	33	22	63
8	1		Brand 7			Jun-60	3	33	22	63
9	0		Brand 9			12/600	1	36	22.5	160					Color 12/Color 111
10	No match		Brand 9			4/613	1	36	22.5	160					Color 4/Color 10
11	1		Brand 9			Dec-60	1	36	22.5	160					Color 12/Color 11
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=LET(row,MATCH(G2,[Reference_Sample_workbook.xlsm]Reference_Sample!$A$2:$A$10,0),result,INDEX([Reference_Sample_workbook.xlsm]Reference_Sample!$H$2:$P$10,row,0),num,INDEX([Reference_Sample_workbook.xlsm]Reference_Sample!$F$2:$N$10,row,0),name,INDEX([Reference_Sample_workbook.xlsm]Reference_Sample!$G$2:$O$10,row,0),FILTER(result,(num=J2)+IF(S2<>"",name=S2),"No match"))
L6:L8	L6	=11+11+11
L9:L11	L9	=36