# RE: Vlookup formula

#### garychaan

##### New Member
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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'

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)

Kindly explain how does it work here.

Thanks.

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.

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.

Replies
2
Views
639
Replies
1
Views
673
Replies
4
Views
447
Replies
7
Views
461
Replies
3
Views
388

1,206,947
Messages
6,075,798
Members
446,158
Latest member
octagonalowl

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back