# Help required with VLOOKUP and IF function combined ?

#### WillM

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

Will M

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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

see:

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

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

Replies
1
Views
400
Replies
3
Views
211
Replies
12
Views
576
Replies
0
Views
426
Replies
9
Views
715

1,218,839
Messages
6,144,782
Members
450,567
Latest member
Mplz

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