insert a "message" in VLOOKUP

[Akashwani]

Good day to you all,

I have the following =VLOOKUP(B5,Master,2,FALSE)

What I want to know is, can I include a "message" after FALSE?

I want a message to come up if the result is incorrect, rather than have #N/A appear.

Ak

 

[Legacy 51064]

=if(iserror(VLOOKUP(B5,Master,2,0)),"Error - No data",VLOOKUP(B5,Master,2,0))

 

[VoG]

Try

=IF(ISNA(VLOOKUP(B5,Master,2,FALSE)),"Message",VLOOKUP(B5,Master,2,FALSE))

 

[Akashwani]

Thank you very much, works a treat.

I spent an hour trying to work this out, and I got a reply on here in 5 minutes, EXCELLENT.

I am a total novice and really do struggle to understand Excel and Excel help, any advice on how to improve my (limited) skills?

Thanks

Ak

 

[Smitty]

Welcome to the Board!

I am a total novice and really do struggle to understand Excel and Excel help, any advice on how to improve my (limited) skills?

Keep plugging away at it. I think you'll find that very few of us have ever taken a class.

I'd recommend looking at the Microsoft Template Gallery and play with templates that you find useful for work/home.

There's also a lot of content at the Microsoft Office Online site.

And you can always ask questions here! That's why the board's here.

HTH,

 

[Akashwani]

Hi, I'm back.

I knew I should have asked this in my original post.

How do I make the "message" show up red?

And can you make the "message" flash???????

Ak (the Boomarang)

 

[VoG]

Colouring and flashing text in the result of a formula is not possible, sorry.

 

[Akashwani]

Ok, thanks anyway.

Ak

 

[VoG]

Actually I wasn't quite correct. You can't flash cells without VBA code but you could colour a cell if it contained a particular value (like your message) using Conditional Formatting http://www.contextures.com/xlCondFormat01.html

 

[gardnertoo]

Colouring and flashing text in the result of a formula is not possible, sorry.

However, you can set up conditional formatting in that cell so that if the cell contents match your message, the cell font is red.