Lookup across multiple columns

[Mister Tom]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Weight (lbs)[/TD]
[TD]Weight Band[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]167[/TD]
[TD]A[/TD]
[TD]156.53[/TD]
[TD]126.79[/TD]
[TD]107.98[/TD]
[TD]90.78[/TD]
[TD]78.94[/TD]
[TD]64.93[/TD]
[/TR]
[TR]
[TD]4993[/TD]
[TD]D[/TD]
[TD]21[/TD]
[TD]18[/TD]
[TD]45[/TD]
[TD]77[/TD]
[TD]39[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]560[/TD]
[TD]C[/TD]
[TD]11[/TD]
[TD]28[/TD]
[TD]63[/TD]
[TD]49[/TD]
[TD]33[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]9998[/TD]
[TD]E[/TD]
[TD]28[/TD]
[TD]30[/TD]
[TD]45[/TD]
[TD]51[/TD]
[TD]70[/TD]
[TD]55[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mister Tom]

how do I look up the rows to return the correct price for each weight (lbs)?

 

[Peter_SSs]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J
1	Weight (lbs)	Weight Band			A	B	C	D	E	F
2	167	A			156.53	126.79	107.98	90.78	78.94	64.93
3	4993	D			21	18	45	77	39	18
4	560	C			11	28	63	49	33	19
5	9998	E			28	30	45	51	70	55
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?

 

[Mister Tom]

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]

Still ..

What are the expected results for the sample data and how do you get them manually?

 

[Mister Tom]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]weight (lbs)[/TD]
[TD]weight Band[/TD]
[TD]Price ($)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]167[/TD]
[TD]A[/TD]
[TD]156.53[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4993[/TD]
[TD]D[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]560[/TD]
[TD]C[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9998[/TD]
[TD]E[/TD]
[TD]70[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]$156.53[/TD]
[TD]$126.79[/TD]
[TD]$107.98[/TD]
[TD]$90.78[/TD]
[TD]$78.94[/TD]
[TD]$64.93[/TD]
[/TR]
[TR]
[TD]$21[/TD]
[TD]$18[/TD]
[TD]$45[/TD]
[TD]$77[/TD]
[TD]$39[/TD]
[TD]$18[/TD]
[/TR]
[TR]
[TD]$11[/TD]
[TD]$28[/TD]
[TD]$63[/TD]
[TD]$49[/TD]
[TD]$33[/TD]
[TD]$19[/TD]
[/TR]
[TR]
[TD]$28[/TD]
[TD]$30[/TD]
[TD]$45[/TD]
[TD]$51[/TD]
[TD]$70[/TD]
[TD]$55[/TD]
[/TR]
</tbody>[/TABLE]



The original tables are

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Weight (lbs)[/TD]
[TD]Weight Band[/TD]
[TD]Price ($)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]weight (lbs)[/TD]
[TD]weight Band[/TD]
[TD]Price ($)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]167[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4993[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]560[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9998[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]0-499[/TD]
[TD]500-999[/TD]
[TD]1000-1999[/TD]
[TD]2000-4999[/TD]
[TD]5000-9999[/TD]
[TD]10000-19999[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]$156.53[/TD]
[TD]$126.79[/TD]
[TD]$107.98[/TD]
[TD]$90.78[/TD]
[TD]$78.94[/TD]
[TD]$64.93[/TD]
[/TR]
[TR]
[TD]$21[/TD]
[TD]$18[/TD]
[TD]$45[/TD]
[TD]$77[/TD]
[TD]$39[/TD]
[TD]$18[/TD]
[/TR]
[TR]
[TD]$11[/TD]
[TD]$28[/TD]
[TD]$63[/TD]
[TD]$49[/TD]
[TD]$33[/TD]
[TD]$19[/TD]
[/TR]
[TR]
[TD]$28[/TD]
[TD]$30[/TD]
[TD]$45[/TD]
[TD]$51[/TD]
[TD]$70[/TD]
[TD]$55

[/TD]
[/TR]
</tbody>[/TABLE]

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]

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?

 

[Mister Tom]

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?