Excel Question about If, Vlookup, and If Error

[shankys_14]

Hi All,

I am trying to accomplish something at work.

Basically what I am trying to do is.

If there is a value in Cell B1, do a vlookup and provide me with the information I need in cell B2. If there is no value in cell B1 leave cell B2 blank as well.

However IF, there is a value in cell B1 and the vlookup returns me an error because whats in cell B1 isn't on my data set of what I am looking up give me a message of "take action"

Thanks!

[Pepe Le Mokko]

In B2 try =IF(B1="","",IF(ISNA(your_formula),"take action", your_formula))

(replace " your_formula" with VLOOKUP(B1,.... )

[shankys_14]

This is the formula I currently have

=IF(B21>0,IFERROR(VLOOKUP(B21,'XYZ'!A:B,2,0),"Take Action")," ")

However even if nothing is in cell B21 I am getting the "Take Action" message

[Sam Capricci]

this will return a value or a message, I've never tried it for all three of an error or a message or a value...
=IF(IFERROR(VLOOKUP(B1,C:C,1,FALSE),"")="","take action",VLOOKUP(B1,C:C,1,FALSE))
this will return a blank or a message...
=IF(IFERROR(VLOOKUP(B1,C:C,1,FALSE),"")="","take action","")

[Teblol]

I attached an example but assuming that your data is in columns D and E use this:

Please Login or Register  to view this content.

Lookup for shankys_14.xlsx

[shankys_14]

I attached an example but assuming that your data is in columns D and E use this:

Please Login or Register  to view this content.

Attachment 441893

Sorry I am still getting take action even tho B1 is empty. Could is be because I have a if formula in B1? The formula I have in B1 is =IF('Xyz'!B39>0,'xyz'!B39,"")

[Teblol]

Try this:

Please Login or Register  to view this content.

[Sam Capricci]

Mine might work for you if you make it this way (untested) =IF(B1="","",if(IFERROR(VLOOKUP(B1,C:C,1,FALSE),"")="","take action",VLOOKUP(B1,C:C,1,FALSE)))

[shankys_14]

Try this:

Please Login or Register  to view this content.

This worked, thank you!

[Teblol]

No problem. Glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

[shankys_14]

Thanks, marked back as unsolved because I didn't want to open a new thread. Is there anyway that not only does it say Take Action it also highlights the cell yellow or red?

[Teblol]

Yes. You can do this with conditional formatting. Select the cell. Go to conditional formatting. New rule. Choose format only cells that contain. Switch cell value to specific text. Type Take Action into the bar. Click format and change the fill to whatever color you want.