Can anyone help me with this Excel question?

[IHAVEAQUESTION]

https://skydrive.live.com/redir?resid=E18117CDA154C70!4560

Two tabs on this workbook. I want the first tab, CLAS_PSAR Recon, the CLAS column (Column I for instance) to load the amount (C column) from the second tab, CLAS, for each date. The way I can think of is by using VLookup, i.e.=VLOOKUP(A6, 'CLAS '!B33:C53, 2,0), and then drag down the formula for each date.

Well, I think it's something with the date format but no matter how I change the format I keep getting N/A as the result. Can someone tell me how I can format the date so that vlookup can work? I am running out of idea.

 

[PowerEngineer]

I try to stay away from VLOOKUP and HLOOKUP because they are so persnickety about the arrangements of the tables. There are a couple of ways I might try to do what (I understand that) you are trying to do.

Here's the easiest using the combination of INDEX and MATCH:

=INDEX('CLAS '!C33:C53, MATCH(A6, 'CLAS '!B33:B53, 0), 1)
​

But I'd be looking to generalize the formula to accommodate both account number and date by using SUMIFS. You'd have to redo your first sheet to break out the account numbers into a separate row (assume row 5 here) so that the formula looks something like this:

=SUMIFS('CLAS '!$C:$C, 'CLAS '!$A:$A, "="&TEXT(?$5,"0"), 'CLAS '!$B:$B, "="&TEXT($A?,"0")

(where the question marks are the row and column where the formula resides)
​

The gist of the above is that the amounts in column C are only included in the sum if both the account number (in column A) matches what is specified in the first sheet's column header (row 5) and the date (in column B) matches what is specified in column A of the first sheet. The nice thing about this (IMHO) is that it works regardless of the ordering of the data on the second sheet and also totals multiple entries.

I haven't actually tested either of these formulas and therefore apologize in advance for any errors in grammar.

 

[cbrunny]

Three things that I've noticed here:

Your date formatting isn't the same between the two sheets, so when you vlookup based on it, one is a date, the other is a string. Looks to me like the dates on CLAS are string.

I'm not familiar with the "0" at the end of your Vlookup function. For my Excel, I use 'FALSE' to force an exact match. I assume this is what you're trying to do. If 0 = FALSE, ignore this comment.

When you drag the formula down for each date, you'll also be shifting the B33:C53 box. By the sounds of things, you want these to remain static, so I'd re-write it as $B$33:$C$53 before you drag down.

Hope this helps!

EDIT: I've used VLOOKUP for years and with "FALSE" appropriated in the formula, and as long as there are no duplicates on the lookup value, I've not run into issues as PowerEngineer is suggesting. That being said, I've heard that comment before. So check your result carefully.