HLOOKUP, VLOOKUP, LOOKUP???

[Connie Martin]

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

 

[Don Guillett]

modify to suit
=IF(ISNA(MATCH(B1,Sheet18!E:E,0)),"NO","Yes")

 

[~L]

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

 

[Glenn]

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

 

[Spiky]

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.

 

[Spiky]

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.

 

[~L]

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.

 

[Connie Martin]

Thank you. That worked! Connie

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

 

[Connie Martin]

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

 

[Connie Martin]

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

 

[Connie Martin]

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