# Excel: IF Statements

Gary Wood 19:48 06 Oct 2005
Locked

I need to write an IF statement with Excel to do the following:

Take a value from a cell (say cell B2), see if this value occurs anywhere in column B on another worksheet and then, if it does leave the cell the formula is in blank and if it doesn't, put an * in the cell.

Looking at the IF statements dialog box I can see how to set the blank cell or the *, but I don't know how to make it compare the value in B2 with a list of values in column B of another sheet.

Can anyone help?

scotty 19:51 06 Oct 2005

Try the LOOKUP function

Gary Wood 19:53 06 Oct 2005

Scotty,

I'm just looking at the Lookup function now but still can't get it to work. I have this formula:

=IF(LOOKUP(C2,Sheet_Two!B:B),"","*")

Can you see anything wrong with this?

Thanks,

Gary

Example number in A1, column to look down is B:B.

=IF(COUNTIF(B:B,A1)>0,"","*")

Hope it helps.

Simsy 20:14 06 Oct 2005

of functions will do this...

Try;

=IF(ISNUMBER(MATCH(B2,Sheet2!B:B,0)),"","*")

Where B2 contains the value you're looking for, and Sheet2Row B is where you want to see if it exists.

Regards,

Simsy

Simsy 20:23 06 Oct 2005

the suggestion from silasgreenback is probably a little tidier than mine...

But by way of explanation what mine does is use 3 functions,

MATCH

ISNUMBER

IF

MATCH takes the value in cell B2 and looks for it in Sheet2ColB. If it finds it the result is what position it is in in that column... i.e. effectively the row number...and ISNUMBER is therfore TRUE...

If it doesn't find it the return from MATCH is FALSE... i.e. it is not a number, so ISNUMBER returns FALSE.

I hope that makes sense...

ANd by the way I've just realised that in my previous post I said ROW when I should have said COL.. Sorry!!

Regards,

Simsy

Simsy 20:29 06 Oct 2005

The problem with your LOOKUP formula is that you dont have enough "arguments"... you only have 2 instead of 3.

the correct syntax for LOOKUP is;

LOOKUP(What_to_look_for,Where_to_look_for_it,Value_to_return_from_Corresponding_range)

You have the 3rd argument missing... because you don't have such a range, which is why the LOOKUP function isn't appropriate in this case.

Check the Excel HELP for lookup and note the comparison with telephone numbers it gives as an explanation.

Hope this helps,

Regards,

Simsy

Gary Wood 20:35 06 Oct 2005

Many thanks Simsy & silasgreenback, this formula has solved the issue I was having and it now works perfectly.

Gary

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…