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>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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
 
Upvote 0
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.

00 Old HTML Conversions 2020-04-08 1.xlsm
ABCDEFGHIJ
1Weight (lbs)Weight BandABCDEF
2167A156.53126.79107.9890.7878.9464.93
34993D211845773918
4560C112863493319
59998E283045517055
Lookup


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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top