Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > look up multiple values, to return only one value
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DP7 DP7 is offline
external usenet poster
  
Posts: 54
Default look up multiple values, to return only one value
I want to look up multiple values, to return only one value. So far I have
only seen that I can use one lookup value in VLOOKUP. Is there any way or any
other function I should use to look up multiple values to return only one
value? The multiple lookup values I want to use are not all in the same row.
The value to be returned is in the rightmost column.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 226
Default look up multiple values, to return only one value
the experts might need to add to this, but I know you can use vlookup in this
scenario, it goes something like this:

=vlookup,A1+B5+C8,value of destination cell,0,1)

so just use the + sign to look for multiple values.

Sorry for lack of detail, I can do it better than I can explain it.

"DP7" wrote:

I want to look up multiple values, to return only one value. So far I have
only seen that I can use one lookup value in VLOOKUP. Is there any way or any
other function I should use to look up multiple values to return only one
value? The multiple lookup values I want to use are not all in the same row.
The value to be returned is in the rightmost column.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,688
Default look up multiple values, to return only one value
You need to post some sample data.

Biff

"DP7" wrote in message
...
I want to look up multiple values, to return only one value. So far I have
only seen that I can use one lookup value in VLOOKUP. Is there any way or
any
other function I should use to look up multiple values to return only one
value? The multiple lookup values I want to use are not all in the same
row.
The value to be returned is in the rightmost column.


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DP7 DP7 is offline
external usenet poster
  
Posts: 54
Default look up multiple values, to return only one value
Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS 50.904


I need to match style, cut and color from Sheet one to Sheet two and insert
the appropriate yield value from sheet 2 into sheet 1.



"Biff" wrote:

You need to post some sample data.

Biff

"DP7" wrote in message
...
I want to look up multiple values, to return only one value. So far I have
only seen that I can use one lookup value in VLOOKUP. Is there any way or
any
other function I should use to look up multiple values to return only one
value? The multiple lookup values I want to use are not all in the same
row.
The value to be returned is in the rightmost column.



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DP7 DP7 is offline
external usenet poster
  
Posts: 54
Default look up multiple values, to return only one value
PLEASE DISREGURAD LAST POST. FORMATING ERROR.

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS ETC.




"DP7" wrote:

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS 50.904


I need to match style, cut and color from Sheet one to Sheet two and insert
the appropriate yield value from sheet 2 into sheet 1.



"Biff" wrote:

You need to post some sample data.

Biff

"DP7" wrote in message
...
I want to look up multiple values, to return only one value. So far I have
only seen that I can use one lookup value in VLOOKUP. Is there any way or
any
other function I should use to look up multiple values to return only one
value? The multiple lookup values I want to use are not all in the same
row.
The value to be returned is in the rightmost column.



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,688
Default look up multiple values, to return only one value
Try this:

Sheet2!A$2:A$19 = STYLE
Sheet2!B$2:B$19 = CUT
Sheet2!C$2:C$19 = COLOR
Sheet2!E$2:E$19 = YIELD

=SUMPRODUCT(--(Sheet2!A$2:A$19=B2),--(Sheet2!B$2:B$19=A2),--(Sheet2!C$2:C$19=C2),Sheet2!E$2:E$19)

Biff

"DP7" wrote in message
...
PLEASE DISREGURAD LAST POST. FORMATING ERROR.

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS ETC.




"DP7" wrote:

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS 50.904


I need to match style, cut and color from Sheet one to Sheet two and
insert
the appropriate yield value from sheet 2 into sheet 1.



"Biff" wrote:

You need to post some sample data.

Biff

"DP7" wrote in message
...
I want to look up multiple values, to return only one value. So far I
have
only seen that I can use one lookup value in VLOOKUP. Is there any
way or
any
other function I should use to look up multiple values to return only
one
value? The multiple lookup values I want to use are not all in the
same
row.
The value to be returned is in the rightmost column.





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DP7 DP7 is offline
external usenet poster
  
Posts: 54
Default look up multiple values, to return only one value
Hi. I don't know if i am doing something wrong. The function is returning all
0. I also try hitting ctl+shift+enter when i enter the function

"Biff" wrote:

Try this:

Sheet2!A$2:A$19 = STYLE
Sheet2!B$2:B$19 = CUT
Sheet2!C$2:C$19 = COLOR
Sheet2!E$2:E$19 = YIELD

=SUMPRODUCT(--(Sheet2!A$2:A$19=B2),--(Sheet2!B$2:B$19=A2),--(Sheet2!C$2:C$19=C2),Sheet2!E$2:E$19)

Biff

"DP7" wrote in message
...
PLEASE DISREGURAD LAST POST. FORMATING ERROR.

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS ETC.




"DP7" wrote:

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS 50.904


I need to match style, cut and color from Sheet one to Sheet two and
insert
the appropriate yield value from sheet 2 into sheet 1.



"Biff" wrote:

You need to post some sample data.

Biff

"DP7" wrote in message
...
I want to look up multiple values, to return only one value. So far I
have
only seen that I can use one lookup value in VLOOKUP. Is there any
way or
any
other function I should use to look up multiple values to return only
one
value? The multiple lookup values I want to use are not all in the
same
row.
The value to be returned is in the rightmost column.






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,688
Default look up multiple values, to return only one value
If you want to, you can send me a copy of your file so I can see what's
needed. I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"DP7" wrote in message
...
Hi. I don't know if i am doing something wrong. The function is returning
all
0. I also try hitting ctl+shift+enter when i enter the function

"Biff" wrote:

Try this:

Sheet2!A$2:A$19 = STYLE
Sheet2!B$2:B$19 = CUT
Sheet2!C$2:C$19 = COLOR
Sheet2!E$2:E$19 = YIELD

=SUMPRODUCT(--(Sheet2!A$2:A$19=B2),--(Sheet2!B$2:B$19=A2),--(Sheet2!C$2:C$19=C2),Sheet2!E$2:E$19)

Biff

"DP7" wrote in message
...
PLEASE DISREGURAD LAST POST. FORMATING ERROR.

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS ETC.




"DP7" wrote:

Sheet 1
CUT STYLE COLOR Yield
247703 1184I72 ST-827
247703 1184I72 ST-1091
247946 1248AA5 BLACK
247946 1248AA5 BROWN
247772 1189AY6 ST-1089
247893 284AD4 ST-857
247893 284AD4 ST-23
247893 284AD4 ST-529
248143 1248AD4 BLACK
248143 1248AD4 HTR/GRY
248110 1184DG7 ST-827
248110 1184DG7 ST-823

Sheet 2
STYLE# CUT# COLOR Yield
1184I72 247703 ST-1091 P 47.484
1184I72 247703 ST-1092 PLUS 47.484
1184I72 247703 ST-1166 PLUS 47.484
1184I72 247703 ST-529 PLUS 47.484
1184I72 247703 ST-827 PLUS 47.484
1248AA5 247946 BLACK PLUS 47.628
1248AA5 247946 BROWN PLUS 47.628
1189AY6 247772 ST-1089 PLUS 46.512
284AD4 247893 ST-23 PLUS 54
284AD4 247893 ST-529 PLUS 56.484
284AD4 247893 ST-857 PLUS 56.484
1248AD4 248143 BLACK PLUS 47.412
1248AD4 248143 BROWN PLUS 47.412
1248AD4 248143 H.GREY PLUS 47.412
1248AD4 248143 H.TAUPE PLUS 47.412
1248AD4 248143 NAVY PLUS 47.412
1184DG7 248110 ST-23 PLUS 50.904
1184DG7 248110 ST-827 PLUS 50.904


I need to match style, cut and color from Sheet one to Sheet two and
insert
the appropriate yield value from sheet 2 into sheet 1.



"Biff" wrote:

You need to post some sample data.

Biff

"DP7" wrote in message
...
I want to look up multiple values, to return only one value. So far
I
have
only seen that I can use one lookup value in VLOOKUP. Is there any
way or
any
other function I should use to look up multiple values to return
only
one
value? The multiple lookup values I want to use are not all in the
same
row.
The value to be returned is in the rightmost column.








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
return multiple corresponding values in excel Chiller Excel Worksheet Functions 5 January 12th 06 05:43 PM
Search multiple values to return single values JANA Excel Worksheet Functions 8 October 27th 05 04:26 PM
Formula to compare multiple rows values based on another column? Murph Excel Worksheet Functions 4 February 21st 05 02:44 AM
Adding multiple cells, return specific values Jim Excel Worksheet Functions 4 December 8th 04 07:26 AM
Lookup values in a list and return multiple rows of data Amanda L Excel Worksheet Functions 2 December 2nd 04 04:48 PM


All times are GMT +1. The time now is 09:58 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement