# Excel: IF Statements

Gary Wood 19:48 06 Oct 05
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 05

Try the LOOKUP function

Gary Wood 19:53 06 Oct 05

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 05

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 05

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 05

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 05

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.

LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…