Looking up data in various columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Maddoktor - 12 Dec 2006 23:41 GMT

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.

Reply to this Message

KC Rippstein - 13 Dec 2006 15:09 GMT

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.

> Hi all,
>
[quoted text clipped - 19 lines]
>
> Thank you in advance.

Reply to this Message

Rate this thread: