Impossible Accounting Issue! How to look up valid account combinations?

[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 bookings

Account Department
250 10
300 6
350 9

Allowed combinations

Account Department
250 1-10
300 5
350 2-6
It becomes more difficult when several combinations can be allowed, as listed above for "Department codes" 1 to 10. For accounts 300 and 350 it should thus return an N/A.

Is this possible?

 

[jmthompson]

It is possible. You would need to create a list with all possible valid combinations, concatenated.

250-1
250-2
250-3
and so on

Then enter a vlookup with the actual bookings

=vlookup(concatenate(Account, "-", Department),list, 2,False)

When it doesn't find a match, it will automatically return the #N/A

 

[rentadane]

No suggestions at all?

 

[jmthompson]

see above post

 

[rentadane]

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, as you previously mentioned.

So the problem is trying to link a vlookup function to a 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

Hope this makes more sense and your help is very much appreciated!!

 

[Zack Barresse]

Hi there,

I'll keep to your thread instead of pm, as I generally do not work strictly from pm's. It's better to keep all information on the board. This way you'll get many experts looking at your information as opposed to just one.

In looking at your post, I have to say it depends on how you have your data structured. Can you give us specific examples of the data in your cells? Also, can you give us exact ranges of where/how your data is stored/structured? What version of Excel are you using? Also, you say you want a vlookup/macro. Do you want a VBA-based solution, or can you keep it formula based? It's generally always better if you can keep it formula-based (IMHO), although if it's something very difficult for formulas, VBA is an option. There isn't enough information here to make that determination yet though. If you are able to manipulate the data structure, we could probably stick to formulas.

If you could have your data structure on your Allowed Combinations sheet something like this:

Col A: Account
Col B: Department

Then list down, each row as a new record/department allowed for that account, that would be easier to work with. For example

A2: 250
B2: 1
A3: 250
B3: 2
A4: 250
B4: 3
...
A11: 250
B11: 10

Make sense? A new record for each allowed combination. This will make looking up with formulas much easier. Think like a database.

HTH

 

[rentadane]

Hi Zack, the account and department are split by columns like a database, so every line has a seperate account code and department.

The problem is that i need a vlookup function on account code in Sheet1, then look for the account code in Sheet2 and return the allowed department code from the validation sheet (Sheet2) in column B2. B2 in Sheet2 is the IF Function =IF(OR(B2>1; B2<8);B2; "#NA")

That IF function then needs to be applied to Column B2 in Sheet1. So effectively the Vlookup function is getting the IF function from Sheet2, but the IF function is being applied to B2 in Sheet1.

I tried with a Vlookup(EVAL( function on the IF function, but to no avail.

Hope this makes more sense, otherwise how can i post the excel sheet in here?

 

[Zack Barresse]

Sorry, doesn't help much more. Still doesn't give the exact information I asked for. Either provide a sample of your data (doesn't need to contain your actual data, but needs the same structure) or give more exact details (just answer the questions I already asked).