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

K

k_mak

New Member
Joined
Sep 27, 2019
Messages
13
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
ABCD
1POBRANDPRODUCTEDITION
200001JESSUP240103RD
300002
400003
500004

<tbody>
</tbody>
*Column Headers(ROW 1) - can NOT move/change/rename.

Data
ABCDEF
1POAREAVERSIONTRADEMARKITEMCOUNTRY
200001NA3RDJESSUP24010US
300002EU2NDJESSUP24010GB
400003NA4THLEGACY20003CA
500004AS5THLEGACY20003CN
600005EU1STJESSUP24010FR

<tbody>
</tbody>

**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!!!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Welcome to MrExcel!


Consider:



Book1
ABCDEFGHIJKLMNOP
1POBRANDPRODUCTEDITIONBRANDPRODUCTEDITIONPOAREAVERSIONTRADEMARKITEMCOUNTRY
21JESSUP240103RD4531NA3RDJESSUP24010US
32JESSUP240102ND2EU2NDJESSUP24010GB
43LEGACY200034TH3NA4THLEGACY20003CA
54LEGACY200035TH4AS5THLEGACY20003CN
65EU1STJESSUP24010FR
Sheet5
Cell Formulas
RangeFormula
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!
 
Upvote 0
Shorter set of formulas:


Book1
ABCDEFGHIJKLMNOP
1POBRANDPRODUCTEDITIONBRANDPRODUCTEDITIONPOAREAVERSIONTRADEMARKITEMCOUNTRY
21JESSUP240103RD4531NA3RDJESSUP24010US
32JESSUP240102ND2EU2NDJESSUP24010GB
43LEGACY200034TH3NA4THLEGACY20003CA
54LEGACY200035TH4AS5THLEGACY20003CN
65EU1STJESSUP24010FR
7
Sheet5
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
Hi Eric,
Wow...your info is way, way, beyond my comprehension. Will have to take the time to review it. Thanks for your assistance!!!
 
Upvote 0
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?
 
Upvote 0
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! :)
 
Upvote 0
k_mak said:
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?
Click to expand...


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.
 
Upvote 0
To answer your question about putting the values in a table instead of an array constant, you can do this:


Book1
ABCDEFGHIJKLMNOP
1POBRANDPRODUCTEDITIONBRANDPRODUCTEDITIONPOAREAVERSIONTRADEMARKITEMCOUNTRY
21JESSUP240103RDBrandProductEdition1NA3RDJESSUP24010US
32JESSUP240102NDTrademarkItemVersion2EU2NDJESSUP24010GB
43LEGACY200034THStyle3NA4THLEGACY20003CA
54LEGACY200035THModel4AS5THLEGACY20003CN
65EU1STJESSUP24010FR
Sheet5
Cell Formulas
RangeFormula
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.
 
Upvote 0
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Custom function to replicate INDEX MATCH MATCH with predefined fixed arrays
Replies
3
Views
1K
DanteAmor
DanteAmor
A
  • Question
Can the LOOKUP_VALUE in a MATCH function be CONCATENATED?
Replies
3
Views
939
artikyulashun
A
R
  • Question
Sumproduct with Index / Match or Vlookup not returning array
2
Replies
11
Views
2K
Marcelo Branco
M
M
  • Question
IF Two or more values found in range on same row
Replies
3
Views
790
Marcelo Branco
M
M
  • Question
VBA - Concatenate Match Offset Loop
2
Replies
15
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,214,545
Messages
6,120,128
Members
448,947
Latest member
test111

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top