RE: Vlookup formula

[garychaan]

I have the following budget data schedule to be extracted and compare against actual data on a monthly and cumulative basis(YTD) in the financial statement.

Monthly Phase Budget data:
Acctcd acct_decs apr_mth may_mth jun_mth
0124 Salary 10000 20000 30000
0126 Overtime 1000 2000 3000
0128 Allowance 100 200 300

Cumulative (YTD) Phase Budget data:
Acctcd acct_decs apr_ytd may_ytd jun_ytd
0124 Salary 10000 30000 60000
0126 Overtime 1000 3000 6000
0128 Allowance 100 300 600

Can we include three conditions in Vlookup formula as criteria for seach/extract data from budget schedule above e.g. Condition (1) look up for period - month or cumulative month (ytd); Condition (2) look up for account code and Condition (3) look up for amount

Your advice would be appreciated.

 

[PaddyD]

see here:

http://www.mrexcel.com/board/viewtopic.php?topic=21288&forum=2

for a few examples. Follow the link for a couple more. My opinion:

1) dget is the easiest way to do multi key lookups if you can set up your data appropriately
2) array entered index/match combination is easily generalisable for > 2 conditions

Post back if you need it a bit more 'step-by-step'

Paddy

 

[garychaan]

Dear PaddyD

Unfortunately my target worksheet is not of the same format as the budget data schedule (Refer to sample format in earlier mail)

Target worksheet format are as follows:
Current Month Cum(YTD)
Act Bud Var Act Bud Var
Salary xx xxx
Overtime xx xxx
Allowance xx xxx

Abbreviation:
Act = Actual
Bud = Budget
Var = Variance
Cum = Cummulative Year-to-Date (YTD)

Does option No. two helps? Please advise.
Kindly explain how does it work here.

Thanks.

 

[garychaan]

Hi

I need to know the logic and purpose of each key represents in the following Multi-key lookup formula....

(1) DGET

(2) Array-enter
(with and without concatenation)

(3) Sumproduct


Thanks alot.

 

[PaddyD]

On 2002-09-10 19:41, garychaan wrote:
Hi

I need to know the logic and purpose of each key represents in the following Multi-key lookup formula....

(1) DGET

(2) Array-enter
(with and without concatenation)

(3) Sumproduct


Thanks alot.

Gary,

with such a braod question, I don't know where to start! How about this - go to the help files for each of the functions & see what they have to say. Set up some dummy data & see if you can get them to work, then post back with specific questions about the bits of the functions you're having problems with.

See here for some detail on array formulas:

http://www.mrexcel.com/tip011.shtml

and here for extensive info on conditional counting / summing in general:

http://www.mrexcel.com/wwwboard/messages/8961.html


Like I said, with questions as braod as the ones you asked it's difficult do do much more than quote the help file at you. Take a look at the excel help files & the links & post back with specific questions.

paddy