A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

vlook up w- multiple variables



 
 
Thread Tools Display Modes
  #1  
Old April 13th, 2010, 04:53 PM posted to microsoft.public.excel.worksheet.functions
chilitodd
external usenet poster
 
Posts: 2
Default vlook up w- multiple variables

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

  #2  
Old April 13th, 2010, 06:13 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default vlook up w- multiple variables

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

"chilitodd" wrote:

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

  #3  
Old April 14th, 2010, 06:22 PM posted to microsoft.public.excel.worksheet.functions
chilitodd
external usenet poster
 
Posts: 2
Default vlook up w- multiple variables

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!

"Tom Hutchins" wrote:

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

"chilitodd" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.