Excel Vlookup or MATCH Comparison Between Multiple Sheets

[harvw]

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]

 

[harvw]

For that, just do a countif(range,TRUE) and compare with a COUNTA() of the same range?

Apologies for the basic question here but can you please explain this using an example? ie, the range I will be searching is from cells A2 to F2 for about 100 rows down and so the range is A2-A100, B2, B100, C2, C100 etc. and I'm comparing with any and all rows in a different sheet.

 

[FDibbins]

It just keeps returning 0. It is not returning true or false.

What does the formula you used here, look like?