Excel Vlookup or MATCH Comparison Between Multiple Sheets

H

harvw

New Member
Joined
Apr 16, 2017
Messages
19
Hi Guys,

Does anyone know how I can lookup the values in one row in sheet 2 (see tables below) and compare this with all rows in sheet 1?

If any of the cell values in one row in sheet 2 equal the values in any of the rows in sheet 1 then I would like to return the values 'TRUE' or 'FALSE' for each cell that is being compared.

Additionally I want to create a separate column which returns either TRUE if all of the values in one row within sheet 2 equal ALL of the values in any row in sheet 2 or else FALSE.

And for sheet 3 I was thinking of being able to do exactly the same thing as I did for sheet 2 but with mismatched columns.

Your help on this would be greatly appreciated!

sheet 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]cd[/TD]
[TD]name[/TD]
[TD]rating[/TD]
[TD]number[/TD]
[TD]client[/TD]
[/TR]
[TR]
[TD]1032444[/TD]
[TD]913[/TD]
[TD]ethe[/TD]
[TD]low[/TD]
[TD]33[/TD]
[TD]ybr[/TD]
[/TR]
[TR]
[TD]9103452[/TD]
[TD]242[/TD]
[TD]nab[/TD]
[TD]high[/TD]
[TD]77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7783455[/TD]
[TD]455[/TD]
[TD]roshe[/TD]
[TD]medium[/TD]
[TD]50[/TD]
[TD]mac[/TD]
[/TR]
[TR]
[TD]2489990[/TD]
[TD]899[/TD]
[TD]stry[/TD]
[TD]low[/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1345662[/TD]
[TD]231[/TD]
[TD]iot[/TD]
[TD]high[/TD]
[TD]40[/TD]
[TD]nre[/TD]
[/TR]
[TR]
[TD]1345662[/TD]
[TD]231[/TD]
[TD]iot[/TD]
[TD]high[/TD]
[TD]90[/TD]
[TD]med[/TD]
[/TR]
</tbody>[/TABLE]


sheet 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]cd[/TD]
[TD]name[/TD]
[TD]rating[/TD]
[TD]number[/TD]
[TD]client[/TD]
[/TR]
[TR]
[TD]1032444[/TD]
[TD]913[/TD]
[TD]ethe[/TD]
[TD]medium[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1032444[/TD]
[TD]913[/TD]
[TD]ethe[/TD]
[TD]high[/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1032444[/TD]
[TD]913[/TD]
[TD]ethe[/TD]
[TD]low[/TD]
[TD]33[/TD]
[TD]ypr[/TD]
[/TR]
[TR]
[TD]9103452[/TD]
[TD]242[/TD]
[TD]nab[/TD]
[TD]high[/TD]
[TD]77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7783455[/TD]
[TD]455[/TD]
[TD]roshe[/TD]
[TD]medium[/TD]
[TD]50[/TD]
[TD]mac[/TD]
[/TR]
[TR]
[TD]2489990[/TD]
[TD]899[/TD]
[TD]stry[/TD]
[TD]low[/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1345662[/TD]
[TD]231[/TD]
[TD]iot[/TD]
[TD]high[/TD]
[TD]35[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1345662[/TD]
[TD]231[/TD]
[TD]iot[/TD]
[TD]high[/TD]
[TD]90[/TD]
[TD]med[/TD]
[/TR]
</tbody>[/TABLE]


sheet 3

[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]rating[/TD]
[TD]number [/TD]
[TD]id[/TD]
[TD]client[/TD]
[TD]cd[/TD]
[/TR]
[TR]
[TD]ethe[/TD]
[TD]low[/TD]
[TD]33[/TD]
[TD]1032444[/TD]
[TD]ypr[/TD]
[TD]913[/TD]
[/TR]
[TR]
[TD]nab[/TD]
[TD]high[/TD]
[TD]77[/TD]
[TD]9103452[/TD]
[TD][/TD]
[TD]242[/TD]
[/TR]
[TR]
[TD]roshe[/TD]
[TD]medium[/TD]
[TD]50[/TD]
[TD]7783455[/TD]
[TD]mac[/TD]
[TD]455[/TD]
[/TR]
[TR]
[TD]stry[/TD]
[TD]low[/TD]
[TD]90[/TD]
[TD]2489990[/TD]
[TD][/TD]
[TD]899[/TD]
[/TR]
[TR]
[TD]iot[/TD]
[TD]high[/TD]
[TD]40[/TD]
[TD]1345662[/TD]
[TD]nre[/TD]
[TD]231[/TD]
[/TR]
[TR]
[TD]iot[/TD]
[TD]high[/TD]
[TD]90[/TD]
[TD]1345662[/TD]
[TD]med[/TD]
[TD]231[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Code: 
[COLOR=#333333]then I would like to return the values 'TRUE' or 'FALSE' for each cell that is being compared. [/COLOR]

Where you need these True /False ?


Regards,
DILIPandey
 
Upvote 0
Try this on sheet 2...
[Table="width:, class:grid"][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]sheet 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]id[/td][td]cd[/td][td]name[/td][td]rating[/td][td]number[/td][td]client[/td][/tr]

[tr][td]
4​
[/td][td]1032444[/td][td]913[/td][td]ethe[/td][td]low[/td][td]33[/td][td]ybr[/td][/tr]

[tr][td]
5​
[/td][td]9103452[/td][td]242[/td][td]nab[/td][td]high[/td][td]77[/td][td][/td][/tr]

[tr][td]
6​
[/td][td]7783455[/td][td]455[/td][td]roshe[/td][td]medium[/td][td]50[/td][td]mac[/td][/tr]

[tr][td]
7​
[/td][td]2489990[/td][td]899[/td][td]stry[/td][td]low[/td][td]90[/td][td][/td][/tr]

[tr][td]
8​
[/td][td]1345662[/td][td]231[/td][td]iot[/td][td]high[/td][td]40[/td][td]nre[/td][/tr]

[tr][td]
9​
[/td][td]1345662[/td][td]231[/td][td]iot[/td][td]high[/td][td]90[/td][td]med[/td][/tr]

[tr][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]id[/td][td]cd[/td][td]name[/td][td]rating[/td][td]number[/td][td]client[/td][/tr]

[tr][td]
13​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
FALSE​
[/td][td]
FALSE​
[/td][td]
FALSE​
[/td][/tr]

[tr][td]
14​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][/tr]

[tr][td]
15​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][/tr]

[tr][td]
16​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][/tr]

[tr][td]
17​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
TRUE​
[/td][td]
FALSE​
[/td][td]
FALSE​
[/td][/tr]
[/table]
A13=A4=INDEX(Sheet1!$A$4:$F$11,MATCH(Sheet2!$A4,Sheet1!$A$4:$A$11,0),MATCH(Sheet2!A$3,Sheet1!$A$3:$F$3,0))

Adjust the sheet1 references for sheet3 for that part
 
Upvote 0
Hi Ford,


Additionally I want to create a separate column which returns either TRUE if all of the values in one row within sheet 2 equal ALL of the values in any row in sheet 2 or else FALSE.

Any ideas on this?
 
Upvote 0
The following formula doesn't appear to work:

A13=A4=INDEX(Sheet1!$A$4:$F$11,MATCH(Sheet2!$A4,Sheet1!$A$4:$A$11,0),MATCH(Sheet2!A$3,Sheet1!$A$3:$F$3,0))
 
Upvote 0
harvw said:
I would need these values in a separate column i.e. cells G2:G7
Click to expand...

You have 6 columns of data and your sample shows 6 rows (presumably more?), how would you want all those comparisons shown in just 6 cells? Or am I missing something here?
 
Upvote 0
harvw said:
Hi Ford,


Additionally I want to create a separate column which returns either TRUE if all of the values in one row within sheet 2 equal ALL of the values in any row in sheet 2 or else FALSE.

Any ideas on this?
Click to expand...

For that, just do a countif(range,TRUE) and compare with a COUNTA() of the same range?
 
Upvote 0
harvw said:
The following formula doesn't appear to work:

A13=A4=INDEX(Sheet1!$A$4:$F$11,MATCH(Sheet2!$A4,Sheet1!$A$4:$A$11,0),MATCH(Sheet2!A$3,Sheet1!$A$3:$F$3,0))
Click to expand...

what doesn't work? Wrong answer? No answer?
Did you adjust my ranges to suite your date?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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