Impossible Vlookup + IF function

[rentadane]

I have the following issue where i have to analyze actual bookings, and ensure they follow the allowed combination criteria. How can i make an excel vlookup/macro which compares the actual bookings to the allowed combinations, and if they dont meet the allowed criteria, returns an N/A?

Actual booking

Account: 250, Organization: 1, Region: 90

Valid Combination

Account: 250, Organization: 2, Region: 01~69

Validation

Account: 250, Organization: IF(OR(cell>1;cell<9); cell;"#NA"), Region: IF(OR(cell>1;cell<69); G4;"#NA")


What i am attempting to do, is add a vlookup on account 250 in the actual bookings sheet, which refers to account 250 in the validation sheet, and returns an organization code IF it is a valid combination, otherwise "#NA". I added the validation sheet to avoid having to type every possible combination.

So the problem is trying to link a vlookup function to an IF function, i.e. only vlookup IF criterion are met, and return the true value. If the above validation would work, it would return Account: 250, Organization: #NA, Region: #NA based on the above actual bookings

 

[schielrn]

Its not advised or suggested that you start a similiar thread to one that you already have related to the same material.

I would suggest sticking to your original thread for help as others there were assisting you:

http://www.mrexcel.com/forum/showthread.php?t=536581