Can the function: MATCH, be combined with OR function???

[k_mak]

Hello All,
New to the forum and kindly request your expertise. Not certain if this question has been posted before.
Specifically, in the function: MATCH(lookup_value, lookup_array, [match_type]), can we combined the "lookup_value" with OR function???
Tried using a VLOOKUP with MATCH; inside the MATCH function, tried adding an OR function as a lookup value...but was not successful.

Summary
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PO[/TD]
[TD]BRAND[/TD]
[TD]PRODUCT[/TD]
[TD]EDITION[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]00001[/TD]
[TD]JESSUP[/TD]
[TD]24010[/TD]
[TD]3RD[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]00002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]00003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]00004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
*Column Headers(ROW 1) - can NOT move/change/rename.

Data
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PO[/TD]
[TD]AREA[/TD]
[TD]VERSION[/TD]
[TD]TRADEMARK[/TD]
[TD]ITEM[/TD]
[TD]COUNTRY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]00001[/TD]
[TD]NA[/TD]
[TD]3RD[/TD]
[TD]JESSUP[/TD]
[TD]24010[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]00002[/TD]
[TD]EU[/TD]
[TD]2ND[/TD]
[TD]JESSUP[/TD]
[TD]24010[/TD]
[TD]GB[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]00003[/TD]
[TD]NA[/TD]
[TD]4TH[/TD]
[TD]LEGACY[/TD]
[TD]20003[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]00004[/TD]
[TD]AS[/TD]
[TD]5TH[/TD]
[TD]LEGACY[/TD]
[TD]20003[/TD]
[TD]CN[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]00005[/TD]
[TD]EU[/TD]
[TD]1ST[/TD]
[TD]JESSUP[/TD]
[TD]24010[/TD]
[TD]FR[/TD]
[/TR]
</tbody>[/TABLE]

**Column Headers(ROW 1)
-Columns can be 10 to 50
-Column headers not in the same location
-Column header names can be different... on Summary, column header must be called: Product. On Data, Column headers can be called: "Product", "Item", "Style" or "Model".

Perhaps VLOOKUP and MATCH won't work, so any help is greatly appreciated! Thank you!!!

 

[Eric W]

Welcome to MrExcel!


Consider:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	PO	BRAND	PRODUCT	EDITION		BRAND	PRODUCT	EDITION			PO	AREA	VERSION	TRADEMARK	ITEM	COUNTRY
2	1	JESSUP	24010	3RD		4	5	3			1	NA	3RD	JESSUP	24010	US
3	2	JESSUP	24010	2ND							2	EU	2ND	JESSUP	24010	GB
4	3	LEGACY	20003	4TH							3	NA	4TH	LEGACY	20003	CA
5	4	LEGACY	20003	5TH							4	AS	5TH	LEGACY	20003	CN
6											5	EU	1ST	JESSUP	24010	FR
Sheet5

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,$K$2:$T$10,AGGREGATE(15,6,(COLUMN($K$1:$T$1)-COLUMN($K$1)+1)/ISNUMBER(MATCH($K$1:$T$1,{"Brand","Trademark"},0)),1),0)
B5	=VLOOKUP(A5,$K$2:$T$10,$F$2,0)
C2	=VLOOKUP(A2,$K$2:$T$10,AGGREGATE(15,6,(COLUMN($K$1:$T$1)-COLUMN($K$1)+1)/ISNUMBER(MATCH($K$1:$T$1,{"Product","Item","Style","Model"},0)),1),0)
C5	=VLOOKUP(A5,$K$2:$T$10,$G$2,0)
D2	=VLOOKUP(A2,$K$2:$T$10,AGGREGATE(15,6,(COLUMN($K$1:$T$1)-COLUMN($K$1)+1)/ISNUMBER(MATCH($K$1:$T$1,{"Edition","Version"},0)),1),0)
D5	=VLOOKUP(A5,$K$2:$T$10,$H$2,0)
F2	=AGGREGATE(15,6,(COLUMN($K$1:$T$1)-COLUMN($K$1)+1)/ISNUMBER(MATCH($K$1:$T$1,{"Brand","Trademark"},0)),1)
G2	=AGGREGATE(15,6,(COLUMN($K$1:$T$1)-COLUMN($K$1)+1)/ISNUMBER(MATCH($K$1:$T$1,{"Product","Item","Style","Model"},0)),1)
H2	=AGGREGATE(15,6,(COLUMN($K$1:$T$1)-COLUMN($K$1)+1)/ISNUMBER(MATCH($K$1:$T$1,{"Edition","Version"},0)),1)

You can use the B2:D2 formulas to do what you want. You'll need to change the ranges, as well as the lists of column headers to look for. However, it might be better to find the right column offset in some helper cells, like I did in G2:H2, then you can use a basic VLOOKUP in your summary table, like the B5:D5 formulas.


Hope this helps!

 

[Eric W]

Shorter set of formulas:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	PO	BRAND	PRODUCT	EDITION		BRAND	PRODUCT	EDITION			PO	AREA	VERSION	TRADEMARK	ITEM	COUNTRY
2	1	JESSUP	24010	3RD		4	5	3			1	NA	3RD	JESSUP	24010	US
3	2	JESSUP	24010	2ND							2	EU	2ND	JESSUP	24010	GB
4	3	LEGACY	20003	4TH							3	NA	4TH	LEGACY	20003	CA
5	4	LEGACY	20003	5TH							4	AS	5TH	LEGACY	20003	CN
6											5	EU	1ST	JESSUP	24010	FR
7
Sheet5

Cell Formulas
Range	Formula
B2	=VLOOKUP(A2,$K$2:$T$10,AGGREGATE(15,6,MATCH({"Brand","Trademark"},$K$1:$S$1,0),1),0)
B5	=VLOOKUP(A5,$K$2:$T$10,$F$2,0)
C2	=VLOOKUP(A2,$K$2:$T$10,AGGREGATE(15,6,MATCH({"Product","Item","Style","Model"},$K$1:$S$1,0),1),0)
C5	=VLOOKUP(A5,$K$2:$T$10,$G$2,0)
D2	=VLOOKUP(A2,$K$2:$T$10,AGGREGATE(15,6,MATCH({"Edition","Version"},$K$1:$S$1,0),1),0)
D5	=VLOOKUP(A5,$K$2:$T$10,$H$2,0)
F2	=AGGREGATE(15,6,MATCH({"Brand","Trademark"},$K$1:$S$1,0),1)
G2	=AGGREGATE(15,6,MATCH({"Product","Item","Style","Model"},$K$1:$S$1,0),1)
H2	=AGGREGATE(15,6,MATCH({"Edition","Version"},$K$1:$S$1,0),1)

It might still be worth considering the helper cells for performance reasons.

 

[DanteAmor]

Welcome to the forum!

Try this

Data
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:23.76px;" /><col style="width:38.02px;" /><col style="width:58.93px;" /><col style="width:89.35px;" /><col style="width:39.92px;" /><col style="width:62.73px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">PO</td><td style="background-color:#ffff00; ">AREA</td><td style="background-color:#ffff00; ">VERSION</td><td style="background-color:#ffff00; ">TRADEMARK</td><td style="background-color:#ffff00; ">ITEM</td><td style="background-color:#ffff00; ">COUNTRY</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >NA</td><td >3RD</td><td >JESSUP</td><td style="text-align:right; ">24500</td><td >US</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >EU</td><td >2ND</td><td >JESSUP</td><td style="text-align:right; ">24010</td><td >GB</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >NA</td><td >4TH</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >CA</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >AS</td><td >5TH</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >CN</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">5</td><td >EU</td><td >1ST</td><td >JESSUP</td><td style="text-align:right; ">24010</td><td >FR</td></tr></table>




Summary
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PO</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">BRAND</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PRODUCT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">EDITION</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >JESSUP</td><td style="text-align:right; ">24500</td><td >3RD</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >JESSUP</td><td style="text-align:right; ">24010</td><td >2ND</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >4TH</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >5TH</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >B2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH({"BRAND","TradeMArk"},Data!$A$1:$F$1,0),0)))}</td></tr><tr><td >C2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH({"PRODUCT","ITEM"},Data!$A$1:$F$1,0),0)))}</td></tr><tr><td >D2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH({"EDITION","VERSION"},Data!$A$1:$F$1,0),0)))}</td></tr></table></td></tr></table>
Fill in each formula the different names of each heading


Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[k_mak]

Hi Eric,
Wow...your info is way, way, beyond my comprehension. Will have to take the time to review it. Thanks for your assistance!!!

 

[k_mak]

Hi Dante,
Thank you for your response. Your formula worked but I do have further question... is it possible to put a range/list from another table between { } of the 2nd MATCH?

 

[Eric W]

It's really not that hard, it just uses some pieces you might not be familiar with. Let's look at the first formula from post 3:

=VLOOKUP(A2,$K$2:$T$10,AGGREGATE(15,6,MATCH({"Brand","Trademark"},$K$1:$S$1,0),1),0)

The basic idea is exactly the VLOOKUP(MATCH formula you're familiar with. But in your case, you need to check for multiple column headings. So instead of just looking for "Brand", we have to look for "Trademark" too. So we put both values in an array constant: {"Brand","Trademark"}. This tells Excel to look up both values, and returns them in another array constant. Here we'd get {#N/A!,4}. Brand is not found so we get the error message, and Trademark is in column 4 of K1:S1. So now we just need to get the 4 out of the array. We can't just use MIN because the #N/A! error will cause that error to propagate. So I used AGGREGATE, which is a newish function, that has several functions built in. The first option, 15, means use the SMALL function. The second option, 6, means Ignore Errors. So the result of that gives us the 4, and then you have the basic VLOOKUP you're familiar with.

There are a few other ways to get the value out of {#N/A!,4}. Dante uses IFERROR to handle the error. We could also use LOOKUP:

=VLOOKUP(A2,$K$2:$T$10,LOOKUP(2^18,MATCH({"Brand","Trademark"},$K$1:$S$1,0)),0)

which gives us a slightly shorter formula.

Hope this helps make sense out of it!

 

[DanteAmor]

Hi Dante,
Thank you for your response. Your formula worked but I do have further question... is it possible to put a range/list from another table between { } of the 2nd MATCH?

Something like this:

The formula in column C will look for the values ​​found in column F

Summary
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PO</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">BRAND</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PRODUCT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">EDITION</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">TYPE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >JESSUP</td><td style="text-align:right; ">24500</td><td >3RD</td><td > </td><td >Product</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >JESSUP</td><td style="text-align:right; ">24010</td><td >2ND</td><td > </td><td >Item</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >4TH</td><td > </td><td >Style</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >5TH</td><td > </td><td >Model</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Arrar Formula</td></tr><tr><td >B2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH({"BRAND","TradeMArk"},Data!$A$1:$F$1,0),0)))}</td></tr><tr><td >C2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH($F$2:$F$5,Data!$A$1:$F$1,0),0)))}</td></tr><tr><td >D2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH({"EDITION","VERSION"},Data!$A$1:$F$1,0),0)))}</td></tr></table></td></tr></table>
Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

 

[Eric W]

To answer your question about putting the values in a table instead of an array constant, you can do this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	PO	BRAND	PRODUCT	EDITION		BRAND	PRODUCT	EDITION			PO	AREA	VERSION	TRADEMARK	ITEM	COUNTRY
2	1	JESSUP	24010	3RD		Brand	Product	Edition			1	NA	3RD	JESSUP	24010	US
3	2	JESSUP	24010	2ND		Trademark	Item	Version			2	EU	2ND	JESSUP	24010	GB
4	3	LEGACY	20003	4TH			Style				3	NA	4TH	LEGACY	20003	CA
5	4	LEGACY	20003	5TH			Model				4	AS	5TH	LEGACY	20003	CN
6											5	EU	1ST	JESSUP	24010	FR
Sheet5

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,$K$2:$T$10,LOOKUP(2^15,MATCH(F$2:F$5,$K$1:$S$1,0)),0)

Put the lists of headers in F2:H5, then you can use the B2 formula. Copy that down and to the right as needed.

 

[DanteAmor]

I corrected the 3 formulas with cell range

Summary
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PO</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">BRAND</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PRODUCT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">EDITION</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">CLASS</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">TYPE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">VER</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >JESSUP</td><td style="text-align:right; ">24500</td><td >3RD</td><td > </td><td >Brand</td><td >Product</td><td >Edition</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >JESSUP</td><td style="text-align:right; ">24010</td><td >2ND</td><td > </td><td >TradeMark</td><td >Item</td><td >Version</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >4TH</td><td > </td><td > </td><td >Style</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >LEGACY</td><td style="text-align:right; ">20003</td><td >5TH</td><td > </td><td > </td><td >Model</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >B2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH($F$2:$F$3,Data!$A$1:$F$1,0),0)))}</td></tr><tr><td >C2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH($G$2:$G$5,Data!$A$1:$F$1,0),0)))}</td></tr><tr><td >D2</td><td >{=INDEX(Data!$A$1:$F$6,MATCH($A2,Data!$A$1:$A$6,0),MAX(IFERROR(MATCH($H$2:$H$3,Data!$A$1:$F$1,0),0)))}</td></tr></table></td></tr></table>