Excel Formulas

[ndukweonum]

Hello folks, good day. Please could anyone help me with this.
I want to write a formula that will return a "YES" or "NO" if a value in a range of cells is a match with a value in another range of cells in another worksheet in the same workbook. That is, for example if a value in Sheet 1 A1:A100 is a match with any value in Sheet 2 A1:A100 return "YES" if not, return "NO". I am kinda confused as to which commands to combine to achieve this.
Any help is much appreciated. thanks

 

[Aladin Akyurek]

In B1 of Sheet1 enter and copy down:

=IF(ISNUMBER(MATCH($A1,Sheet2!$A$1:$A$100,0)),"YES","NO")

 

[ndukweonum]

Ok,thanks for the response, but maybe i need to give you more information, now the values in the cell ranges are in this format "106-99-0", and and there are 100 in the first range and i need to match it up with the 100 in the sheet 2 to return a yes or no if there is a match.

 

[Aladin Akyurek]

Ok,thanks for the response, but maybe i need to give you more information, now the values in the cell ranges are in this format "106-99-0", and and there are 100 in the first range and i need to match it up with the 100 in the sheet 2 to return a yes or no if there is a match.

Care to post 5 rows from Sheet2?

Then 5 rows from Sheet1 along with the expected results?

 

[ndukweonum]

table in sheet1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Colum A[/TD]
[TD]Colum B[/TD]
[TD]Colum C[/TD]
[/TR]
[TR]
[TD]126-99-8[/TD]
[TD][TABLE="width: 264"]
<tbody>[TR]
[TD="colspan: 22"]Chloroprene (liquid monomer)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 253"]
<tbody>[TR]
[TD="colspan: 21"]Group 1 Peroxide former[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="colspan: 2"]628-55-7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 264"]
<tbody>[TR]
[TD="colspan: 22"]Di-iso-butyl ether[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 253"]
<tbody>[TR]
[TD="colspan: 21"]Group 1 Peroxide former[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="colspan: 2"]821-08-9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 264"]
<tbody>[TR]
[TD="colspan: 22"]Divinylacetylene (DVA)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 253"]
<tbody>[TR]
[TD="colspan: 21"]Group 1 Peroxide former[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="colspan: 2"]109-92-9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 264"]
<tbody>[TR]
[TD="colspan: 22"]Ethyl vinyl ether[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 253"]
<tbody>[TR]
[TD="colspan: 21"]Group 1 Peroxide former[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Colum A for sheet2
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="colspan: 2"]62-74-8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="colspan: 2"]7601-89-0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="colspan: 2"]7631-99-4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Now this is what the two sheets look like.I just want a formula that basically can look at the inputs in colum A in sheet2 and return "YES" if it is found in colum A sheet1 or "NO" if it is not.

Hope this is clearer now.Thanks

 

[Aladin Akyurek]

It's thus the way around...

In B1 of Sheet2 enter and copy down:

=IF(ISNUMBER(MATCH($A1,Sheet1!$A$1:$A$100,0)),"YES","NO")

For the sample you posted I don't see if anything from Sheet2 is available in Sheet1.
​

 

[ndukweonum]

i just pulled out a sample from the table in sheet 1. sheet 2 is going to be a kind of table that users will fill out. So if the input any value on sheet 2 that corresponds to any of the values on the columA in sheet one,formula returns "YES",if not the formula just returns "NO"...that is what i mean.

 

[Aladin Akyurek]

i just pulled out a sample from the table in sheet 1. sheet 2 is going to be a kind of table that users will fill out. So if the input any value on sheet 2 that corresponds to any of the values on the columA in sheet one,formula returns "YES",if not the formula just returns "NO"...that is what i mean.

That's what the formula does...

 

[FlexMind]

=IF(ISNUMBER(MATCH($A1,Sheet1!$A$1:$A$100,0)),"YES","NO")
​

Works for me.
Another one:

=IF(IFERROR(VLOOKUP(A1,Sheet1!A:A,1,0),"NOTFOUND")="NOTFOUND","NO","YES")