Please help. I have looked online and can't find info for formula

[letterly]

Hello,

So lets call the data below is on Sheet 1. I would like to create a formula next to each serial number and FIND serial number on Sheet 2. Then, I would then like to compare the values left of the serial numbers of both sheets and return a value of Match or No Match on Sheet 1. I don't If that can be done with one formula or multiple formulas in multiple columns on Sheet 1. Please help if you can.

 

[SanjayGMusafir]

This can be achieved using XLOOKUP like below -

Ifs(Xlookup(your lookup on Sheet 2)=Cell.Address to Match,"Match", True,"Not Match")

This would need 1 column per checked Match

 

[letterly]

This can be achieved using XLOOKUP like below -

Ifs(Xlookup(your lookup on Sheet 2)=Cell.Address to Match,"Match", True,"Not Match")

This would need 1 column per checked Match

I want to match all five columns AFTER the serial number is found. The MATCH depends on result from find formula. I need this to be dynamic since because of sorting and data input, location of serial number will always change.

 

[SanjayGMusafir]

I want to match all five columns AFTER the serial number is found. The MATCH depends on result from find formula. I need this to be dynamic since because of sorting and data input, location of serial number will always change.

I have answered for the same only. Create 5 helper columns on sheet 1 and do as I suggested. Once you have data in 5 helper columns use as you want. It's dynamic as you want...

 

[letterly]

Hello,

So lets call the data below is on Sheet 1. I would like to create a formula next to each serial number and FIND serial number on Sheet 2. Then, I would then like to compare the values left of the serial numbers of both sheets and return a value of Match or No Match on Sheet 1. I don't If that can be done with one formula or multiple formulas in multiple columns on Sheet 1. Please help if you can.
View attachment 94775

I want to match all five columns AFTER the serial number is found. The MATCH depends on result from find formula. I need this to be dynamic since because of sorting and data input, location of serial number will always change.

This is what I have so far and tried. I don't know how to make the formula on right dynamic and change range based on result of formula to left.

 

[SanjayGMusafir]

This is what I have so far and tried. I don't know how to make the formula on right dynamic and change range based on result of formula to left.
View attachment 94777

Can't help with the minimal information you are providing. Might be someone better than me would be able to help you.

 

[AhoyNC]

Try:
Sheet 1

Book1
	A	B	C	D	E
1	Name	Model Location	Version	Serial No.
2	Carson	3400 Colorado	1.2	3621120103	Match
3	John	3400 Colorado	1.2	3620420917	Match
4	Amy	3400 Colorado	1.2	3620430170	Match
5	Jack	3400 Colorado	1.2	3620431115	No Match
6	Beth	3400 Colorado	1.2	3620431186	No Match
7	Rose	3400 Arizona	1.2	3621180016	Match
8	Jason	3400 Arizona	1.2	3636023456	Match
Sheet1

Cell Formulas
Range	Formula
E2:E8	E2	=IF(PRODUCT(--(FILTER(Sheet2!$A$2:$D$8,D2=Sheet2!$D$2:$D$8,"No Match")=A2:D2))=1,"Match","No Match")

Sheet 2

Book1
	A	B	C	D
1	Name	Model Location	Version	Serial No.
2	Carson	3400 Colorado	1.2	3621120103
3	Sam	3400 Colorado	1.2	3620431115
4	Amy	3400 Colorado	1.2	3620430170
5	John	3400 Colorado	1.2	3620420917
6	Beth	3444 Colorado	1.2	3620431186
7	Jason	3400 Arizona	1.2	3636023456
8	Rose	3400 Arizona	1.2	3621180016
Sheet2

 

[letterly]

AhoyNC,

Thank you. The formula doesn't do anything. It treats it like text.

 

[AhoyNC]

Not sure what you mean by "treats it like text". What does "doesn't do anything" mean, do you get a blank, an error or what? Is the example I posted what your data looks like on sheet 1 & 2?
Did you change ranges to match your data?

 

[letterly]

Not sure what you mean by "treats it like text". What does "doesn't do anything" mean, do you get a blank, an error or what? Is the example I posted what your data looks like on sheet 1 & 2?
Did you change ranges to match your data?

It returns a blank cell. It doesn't return n/a , name, or any other typical error message.