Lookup across multiple columns

Mister Tom

New Member
Joined
Nov 12, 2019
Messages
7
Hello I'm new to the forum and look forward to your insights

I have the weight band in column D and want to look up the prices in columns E to J how do I return the right price for each row?

See below

Weight (lbs)Weight BandABCDEF
167A156.53126.79107.9890.7878.9464.93
4993D211845773918
560C112863493319
9998E283045517055

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,605
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

It isn't clear to me.
What are the expected results for the sample data and how do you get them manually?
 

Mister Tom

New Member
Joined
Nov 12, 2019
Messages
7
Welcome to the MrExcel board!

It isn't clear to me.
What are the expected results for the sample data and how do you get them manually?

I'm not sure how to attach the spread sheet. I want to identify how much 167 lbs cost by using the Wt. Band "A" to search the prices in A through F on the first row
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,605
Office Version
365
Platform
Windows
I'm not sure how to attach the spread sheet.
You cannot attach an actual workbook, but you can show small screen shots like I have here. My signature block below has help with that.

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">Weight (lbs)</td><td style=";">Weight Band</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">A</td><td style="text-align: right;;">B</td><td style="text-align: right;;">C</td><td style="text-align: right;;">D</td><td style="text-align: right;;">E</td><td style="text-align: right;;">F</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">167</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">156.53</td><td style="text-align: right;;">126.79</td><td style="text-align: right;;">107.98</td><td style="text-align: right;;">90.78</td><td style="text-align: right;;">78.94</td><td style="text-align: right;;">64.93</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">4993</td><td style=";">D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">21</td><td style="text-align: right;;">18</td><td style="text-align: right;;">45</td><td style="text-align: right;;">77</td><td style="text-align: right;;">39</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">560</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;;">28</td><td style="text-align: right;;">63</td><td style="text-align: right;;">49</td><td style="text-align: right;;">33</td><td style="text-align: right;;">19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">9998</td><td style=";">E</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">28</td><td style="text-align: right;;">30</td><td style="text-align: right;;">45</td><td style="text-align: right;;">51</td><td style="text-align: right;;">70</td><td style="text-align: right;;">55</td></tr></tbody></table><p style="width:8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Lookup</p><br /><br />



I want to identify how much 167 lbs cost by using the Wt. Band "A" to search the prices in A through F on the first row
Unfortunately that doesn't answer the question..
What are the expected results for the sample data and how do you get them manually?
So ..

1. Do I the right layout above? If not what should it be?

2. What is the answer(s) to my previous question?
 

Mister Tom

New Member
Joined
Nov 12, 2019
Messages
7
Hello Peter

Your layoutis right- I have column A,B & C in one sheet and E through J in a second sheet
I'm trying to lookup the value of B2:B5 in sheet 2 to return the value in column C
Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,605
Office Version
365
Platform
Windows
Still ..
What are the expected results for the sample data and how do you get them manually?
 
Last edited:

Mister Tom

New Member
Joined
Nov 12, 2019
Messages
7
I want to find the price of each weight based on the band in column C - I'm using the formula HLOOKUP(B1,Sheet2!$E$1:$J$4,ROW()-1,FALSE) but get some wrong pricing (I have 8,000+ rows:))

Sheet 1
ABC
weight (lbs)weight BandPrice ($)
1167A156.53
24993D77
3560C63
49998E70

<tbody>
</tbody>

Sheet 2
EFGHIJ
ABCDEF
$156.53$126.79$107.98$90.78$78.94$64.93
$21$18$45$77$39$18
$11$28$63$49$33$19
$28$30$45$51$70$55

<tbody>
</tbody>



The original tables are

Sheet 1
ABC
Weight (lbs)Weight BandPrice ($)
weight (lbs)weight BandPrice ($)
1167A
24993D
3560C
49998E

<tbody>
</tbody>

Sheet 2
EFGHIJ
0-499500-9991000-19992000-49995000-999910000-19999
ABCDEF
$156.53$126.79$107.98$90.78$78.94$64.93
$21$18$45$77$39$18
$11$28$63$49$33$19
$28$30$45$51$70$55


<tbody>
</tbody>

I found the weight band in Sheet 1 column B using the formula :

IF(A1<=500,"A",IF(A1<=999,"B",IF(A1<1999,"C",IF(A1<4999,"D",IF(A1<=9999,"E",IF(A1<=19999,"F","A"))))))

Now I need to find the actual cost per lbs.

Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,605
Office Version
365
Platform
Windows
OK, thanks for the additional information.
Now
- For weight 167 and weight band A, why is the answer 156.53 and not 21 or 11 or 28?
- For weight 4993 and weight band D, why is the answer 77 and not 90.78 or 49 or 51?
- etc



And can you confirm that if the weight is greater than 19999 then the weight band should revert to "A" which is normally for small weights 0-499?
 
Last edited:

Mister Tom

New Member
Joined
Nov 12, 2019
Messages
7
Hi Peter I want to search only a specific row in sheet 2 to return the cost
No I don't want wight bands greater than 19999 reverting to A
OK, thanks for the additional information.
Now
- For weight 167 and weight band A, why is the answer 156.53 and not 21 or 11 or 28?
- For weight 4993 and weight band D, why is the answer 77 and not 90.78 or 49 or 51?
- etc



And can you confirm that if the weight is greater than 19999 then the weight band should revert to "A" which is normally for small weights 0-499?
 

Watch MrExcel Video

Forum statistics

Threads
1,089,891
Messages
5,411,055
Members
403,338
Latest member
LynBrown

This Week's Hot Topics

Top