Looking up data in various columns

[Maddoktor]

Hi all,

It is possible to do the following:

Column A Column B Column C Column D Column E
123 456 No
789 Yes

I would like to enter a three digit number i.e. 123 in column A and for
it then to lookup for the identical number in Column C and return the
number in Column D to Column B if Column E equals "No".

If I was to enter the number 123 in Column A and Column E equals "Yes"
then I want to notify the user via a message box (if possible) that this
number is incorrect and needs to be re-entered.

If I was to enter the number 123 in Column A and the number doesn't
appear in Column C, then the data validation would notify the user with
a message box that this number does not exist.

I look forward to any reply that may help me with the above request.

Thank you in advance.

 

[KC Rippstein]

I don't personally do anything with VBA (message boxes) unless I have to so
that others do not have to worry about macros and security settings. I
think you could accomplish what you want by having the error message appear
in column B instead of in a message box. If this is acceptable to you, then
you could accomplish everything you want within cell B2.

In B2, type the following formula (change 99 to the highest row reference
you'll need for your worksheet):
=IF(ISBLANK($A2),"",IF(ISERROR(MATCH($A2,$C$2:$C$99,0)),"NOT
FOUND",IF(VLOOKUP($A2,$C$2:$E$99,3,0)="Yes","INVALID
ENTRY",VLOOKUP($A2,$C$2:$D$99,2,0))))

We told vlookup that column C may or may not be sorted, and matching what's
in cell $A2 means you can drag the formula down to B3, B4, etc. if needed
and it will look in A3, A4, etc.