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

Colum A	Colum B	Colum C
126-99-8	Chloroprene (liquid monomer) <tbody> </tbody>	Group 1 Peroxide former <tbody> </tbody>
628-55-7 <tbody> </tbody>	Di-iso-butyl ether <tbody> </tbody>	Group 1 Peroxide former <tbody> </tbody>
821-08-9 <tbody> </tbody>	Divinylacetylene (DVA) <tbody> </tbody>	Group 1 Peroxide former <tbody> </tbody>
109-92-9 <tbody> </tbody>	Ethyl vinyl ether <tbody> </tbody>	Group 1 Peroxide former <tbody> </tbody>

<tbody>
</tbody>

Colum A for sheet2

62-74-8 <tbody> </tbody>

7601-89-0 <tbody> </tbody>

7631-99-4 <tbody> </tbody>

<tbody>
</tbody>

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