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

id	cd	name	rating	number	client
1032444	913	ethe	low	33	ybr
9103452	242	nab	high	77
7783455	455	roshe	medium	50	mac
2489990	899	stry	low	90
1345662	231	iot	high	40	nre
1345662	231	iot	high	90	med

<tbody>
</tbody>

sheet 1

id	cd	name	rating	number	client
1032444	913	ethe	medium	40
1032444	913	ethe	high	23
1032444	913	ethe	low	33	ypr
9103452	242	nab	high	77
7783455	455	roshe	medium	50	mac
2489990	899	stry	low	90
1345662	231	iot	high	35
1345662	231	iot	high	90	med

<tbody>
</tbody>

sheet 3

name	rating	number	id	client	cd
ethe	low	33	1032444	ypr	913
nab	high	77	9103452		242
roshe	medium	50	7783455	mac	455
stry	low	90	2489990		899
iot	high	40	1345662	nre	231
iot	high	90	1345662	med	231

<tbody>
</tbody>

 

[DILIPandey]

[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

 

[FDibbins]

Try this on sheet 2...

	A​	B​	C​	D​	E​	F​
1​	sheet 2
2​
3​	id	cd	name	rating	number	client
4​	1032444	913	ethe	low	33	ybr
5​	9103452	242	nab	high	77
6​	7783455	455	roshe	medium	50	mac
7​	2489990	899	stry	low	90
8​	1345662	231	iot	high	40	nre
9​	1345662	231	iot	high	90	med
10​
11​
12​	id	cd	name	rating	number	client
13​	TRUE​	TRUE​	TRUE​	FALSE​	FALSE​	FALSE​
14​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​
15​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​
16​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​	TRUE​
17​	TRUE​	TRUE​	TRUE​	TRUE​	FALSE​	FALSE​

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

 

[harvw]

I would need these values in a separate column i.e. cells G2:G7

 

[harvw]

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?

 

[harvw]

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))

 

[FDibbins]

I would need these values in a separate column i.e. cells G2:G7

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?

 

[FDibbins]

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?

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

 

[FDibbins]

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))

what doesn't work? Wrong answer? No answer?
Did you adjust my ranges to suite your date?

 

[harvw]

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