Multiple Condition Vlookups

[quakeystand]

I'm trying to find a way to perform Vlookups with multiple conditions; i.e. If the date matches, and the account code matches, then post the cost data in this cell. I can get the simple Vlookup and IF statements to work individually, but when I try and combine them, it doesn't work. I'm sure more explanation will be needed, but in general, is this kind of problem better solved in VB, or can excel do this? If excel can do this, how do I do it?

E.G.:

From our database:
Date Account Code Cost
3/22/2009 207.89 3000.00
3/22/2009 207.91 18500.00
3/22/2009 207.97 458.00
3/23/2009 207.89 3000.00
3/23/2009 207.91 18500.00
3/23/2009 207.92 187000.00

As you can see, there are unique entries on each day, but not all same account codes are entered daily. I'm trying to lookup and sort this data in another worksheet that does not have the same column/row dimensions. Specifically, I have over 50 account codes to choose from, but only 15 to 20 are used on a daily basis, but not the same 15 to 20 every day. I would like my spreadsheet to look something like this:

Code 3/22/2009 3/23/2009
207.89 3000.00 3000.00
207.91 18500.00 18500.00
207.92 0.00 (or " ") 187000.00
207.93 0.00 0.00
207.95 0.00 0.00
207.97 458.00 0.00

Sorry, my spaces don't carry over well to the posting window...

Thanks,

 

[xld]

Try this array formula

=INDEX(C2:C200,MATCH(1,(A2:A200=some_date)*((B2:B200=account_code),0))

This is an array formula.
This means that you use Ctrl-Shift-Enter to commit the formula, not just Enter (array Enter it). Excel will put curly brackets around the formula in the formula bar, you don#t do this.
If you need to change the formula at any time, you must array Enter it again.

 

[quakeystand]

xld,

Thanks for your help. I can't seem to get it to work... #N/A is returned.

Of course, I may not entirely understand the context of the formula, and thus, have it wrong.

Quakeystand

 

[xld]

Did you array-enter it?

 

[quakeystand]

xld,

Actually, I didn't do it correctly. I re-entered everything, including the CSE, and it works. Thanks.

Now I'll figure out how to pre-filter the errors out of the mix so I can add the daily totals, or figure out a sumif, to not count the error results.

Thanks for your help.

Quakeystand