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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

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

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…