vlook up w- multiple variables

C

chilitodd

For my vlook up in excel 2007, I want to return data to populate cell B2
(xxxx) from column 3 ($21,745) of a data set when the value in cell A2 (360
19318) AND B1 (200801) match from the original data set.

DATA SET
1 2 3
Dosfrom OP_Combined Claimed_Avg
200801 360 19318 $21,745
200803 360 19318 $22,462
200805 360 19318 $25,527
200806 360 19318 $22,462


A B c D
1 Row Labels 200801 200802 200803
2 360 19318 xxxx
3 360 47562
4 480 93620
 
T

Tom Hutchins

Here is one way. This example assumes your data set headings start in A1 on
Sheet1, and your range with the lookup formulas starts in A1 on another
sheet. This formula would go in B2 (adjust the ranges as needed):

=SUMPRODUCT(--(Sheet1!$A$2:$A$15=B$1),--(Sheet1!$B$2:$B$15=$A2),Sheet1!$C$2:$C$15)

Copy this formula across & down as needed. You could get the same results by
creating a pivot table with the OP_Combined as a row field, Dosfrom as a
column field, and Claimed_Avg as the data field.

Hope this helps,

Hutch
 
C

chilitodd

Yes, this worked perfectly! Thank you very, very, very much!!! Based on my
data set, I naturally had to make some changes to the sheet names and cell
references, but once that was fixed up - it worked like a charm!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top