I am confused. In Cell A3, I would like a formula that would look at the
number A4 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet. Simple? Wish it were for me. Help, please! Connie

modify to suit
=IF(ISNA(MATCH(B1,Sheet18!E:E,0)),"NO","Yes")
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
I am confused. In Cell A3, I would like a formula that would look at the
number A4 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet. Simple? Wish it were for me. Help, please! Connie

Try
=IF(COUNTIF(Nirav!$A$1:$A$300,A4)0,"Yes","No")



"Connie Martin" wrote:

I am confused. In Cell A3, I would like a formula that would look at the
number A4 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet. Simple? Wish it were for me. Help, please! Connie

Connie Martin wrote:
I am confused.

You're probably not the only one!

In Cell A3, I would like a formula that would look at the
number A4

I assume you mean "look at the number IN A4".

and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet.

If you want "Yes" or "No" in A2, then what do you want for a result in A3? If
the A2 is just a typo and you really meant A3 (or the other way around), then
you are looking for something like this:

=IF(ISNA(VLOOKUP(A4,Nirav!A1:A300,1,FALSE)),"No"," Yes")


Simple? Wish it were for me. Help, please! Connie

On Oct 23, 11:04*am, Connie Martin
wrote:
I am confused. *In Cell A3, I would like a formula that would look at the
number A4 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet. *Simple? *Wish it were for me. *Help, please! *Connie

Easiest way is:
=IF(A3='Nirav'!A1:A300,"YES","NO")

Hold down CTRL and SHIFT when you press enter.

Sorry, that should be this, didn't read correctly:
=IF(A4='Nirav'!A1:A300,"YES","NO")

Also, you need to put this in whichever cell you want it to be. If you
want YES in A3, put this in A3. If you want it in A2, put it in A2.

Don't forget CTRL+SHIFT+Enter.

Of course, none of these functions will output the result to A2. If you want
the result in A2, you need the formula in A2.

No function will display results in a different cell (that cell needs its
own formula to tell it what to display).

You could always refer A2 to A3 by putting =A3 in A2, but it would be
simpler to have the function in A2 and not A3.

"~L" wrote:

Try
=IF(COUNTIF(Nirav!$A$1:$A$300,A4)0,"Yes","No")



"Connie Martin" wrote:

I am confused. In Cell A3, I would like a formula that would look at the
number A4 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet. Simple? Wish it were for me. Help, please! Connie

Thank you. That worked! Connie

"Don Guillett" wrote:

modify to suit
=IF(ISNA(MATCH(B1,Sheet18!E:E,0)),"NO","Yes")
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Connie Martin" wrote in message
...
I am confused. In Cell A3, I would like a formula that would look at the
number A4 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet. Simple? Wish it were for me. Help, please! Connie

You had every right to be confused!! I should've said B3 instead of A4.
Don't know where my head was! I should've read it over before posting it.
My apologies. However, your formula worked by just changing the A4 to B3.
Thank you! Connie

"Glenn" wrote:

Connie Martin wrote:
I am confused.

You're probably not the only one!

In Cell A3, I would like a formula that would look at the
number A4

I assume you mean "look at the number IN A4".

and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet.

If you want "Yes" or "No" in A2, then what do you want for a result in A3? If
the A2 is just a typo and you really meant A3 (or the other way around), then
you are looking for something like this:

=IF(ISNA(VLOOKUP(A4,Nirav!A1:A300,1,FALSE)),"No"," Yes")


Simple? Wish it were for me. Help, please! Connie

Spiky, sorry, I posted my question incorrectly. It's been very hectic at my
desk, and I should've re-read my question before posting! My question
should've read like this:

In Cell A3, I would like a formula that would look at the
number B3 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A3 of the first
worksheet.

Connie

"Spiky" wrote:

Sorry, that should be this, didn't read correctly:
=IF(A4='Nirav'!A1:A300,"YES","NO")

Also, you need to put this in whichever cell you want it to be. If you
want YES in A3, put this in A3. If you want it in A2, put it in A2.

Don't forget CTRL+SHIFT+Enter.

Thank you. Your formula works. Yes, I made some mistakes in my question. I
should've re-read it before posting it. It was confusing. Thank you! Connie

"~L" wrote:

Of course, none of these functions will output the result to A2. If you want
the result in A2, you need the formula in A2.

No function will display results in a different cell (that cell needs its
own formula to tell it what to display).

You could always refer A2 to A3 by putting =A3 in A2, but it would be
simpler to have the function in A2 and not A3.

"~L" wrote:

Try
=IF(COUNTIF(Nirav!$A$1:$A$300,A4)0,"Yes","No")



"Connie Martin" wrote:

I am confused. In Cell A3, I would like a formula that would look at the
number A4 and then go to the next worksheet called 'Nirav' and see if the
number appears in A1:A300, and return "Yes" or "No" in A2 of the first
worksheet. Simple? Wish it were for me. Help, please! Connie