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]

 

[Peter_SSs]

Hi Peter I want to search only a specific row in sheet 2 to return the cost

You haven't said how to choose the specific row so I'll take a guess that it is simply that for the first formula you want to search the first row of the actual data in Sheet2, for the 2nd formula search the 2nd row etc. If that is not it you will have to specify how I am to determine which row to search. Try this, copied down.

BTW, I get different results to you in row 4 as it seems to me that 560 should give a band of B, not C as shown in post 8. Again, you would need to clarify why if it really should be C.

C2: =INDEX(Sheet2!E$3:J$10000,ROWS(C$2:C2),MATCH(B2,Sheet2!E$2:J$2,0))

00 Old HTML Conversions 2020-04-08 1.xlsm
	A	B	C
1	Weight (lbs)	Weight Band	Price
2	167	A	156.53
3	4993	D	77
4	560	B	28
5	9998	E	70
Sheet1

No I don't want wight bands greater than 19999 reverting to A

I asked about this because your formula at the bottom of post 8 would return "A" if the weight was > 19999.
If it is possible for your weights to be that big, you will have to decide what results you want for that.

 

[Mister Tom]

You are correct the nth formula should search the nth row of the actual data in Sheet2.
560 should give a band of B, not C
No weight exceeds 19999

I'm missing the proposed solution, it appears as a code Could you please resend

 

[exoticdisease]

Use an index/match function.

=INDEX(Sheet2!$E$1:$J$6,3,MATCH(B2,Sheet2!$E$2:$J$2,0))

This assumes that in sheet2 the record "0 - 499" is in E1 etc...

This assumes that in sheet1 the weight "167" is in A2 and the weight band "A" is in B2 etc

 

[Peter_SSs]

I'm missing the proposed solution, it appears as a code Could you please resend

That was due to the forum changing platforms over the weekend. The image should get corrected eventually, but here it is in the new format.

Book1
	A	B	C
1	Weight (lbs)	Weight Band	Price
2	167	A	156.53
3	4993	D	77
4	560	B	28
5	9998	E	70
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=INDEX(Sheet2!E$3:J$10000,ROWS(C$2:C2),MATCH(B2,Sheet2!E$2:J$2,0))