Help required with VLOOKUP and IF function combined ?

WillM

New Member
Joined
Jun 12, 2002
Messages
27
I'm setting up a spreadsheet that will allow inputs from 15 bank accounts to be consolidated to produce details of the ACTUAL cash flows of the organisation i work for.

In sheet 1 my idea is to have an "database" type arrangement with the following columns;

A : Date
B: Category of Income/Expenditure
C : Value (£)
D : Bank account (any 1 of 15 !)

The second sheet will have dates along row 1.

Column A will contain the different categories of Income and expenditure.

I need the calculation to be if date in Sheet 2 , row 1 = date in column B , Sheet 1.....look up the income/expend category (ie Col A , SHEET 2 TO COL B , Sheet 1)..and put £ value.

If that makes any sense whatsover !

Thanks in advance :wink:

Will M
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Will
Rather than using a combination of Vlookup & If, I think you need to use an array (or "CSE" for Ctrl+Shift+Enter) formula which allows two conditions (match date AND category => sum value) with the Sumif function instead of just the standard one.

I have seen this done somewhere but can't put my finger on the details just now (will keep looking) but in the meantime you might look into this idea.

Cheers
BigC
 
Upvote 0
see:

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

for info on conditional counting / summing. Follow the link to aladin's post.

An eg that kind of fits what you described is below, although in your situation a pivot table would probably be more appropriate.
Book6.xls
ABCDE
1DateCat$Bank
21/02/2002A10a
32/02/2002A15b
43/02/2002B20c
51/03/2002B25a
62/03/2002C30b
73/03/2002C35c
81/04/2002A40a
92/04/2002B45b
103/04/2002C50c
111/05/2002A55a
12
13
14Jan-02Feb-02Mar-02
15A0250
16B02025
17C0065
18
Sheet3
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top