Lookup price formula
 

I have a work sheet with prices on it. Across the top are widths down the
side is length.
24 30 36 ............................120
24 $1.00 $2.00 $3.33

30 2.20 3.20 4.20
..
..
120

On another work sheet i have a width and length cells when i enter sizes i
want it to return price. EG width might be 26 and length maybe 27 i want to
return price of 3.20. does anyone know how to do this. i new to excel so keep
it simply please if possible

Lookup price formula
 

Hello Jim,

Looks like you want to round up to the next width or length shown, try this

If your table is in sheet2 with widths in B1:J1 and lengths in A2:A10 and
prices in B2:J10 then in sheet1 with a specific width in A1 (e.g. 26 in your
example) and a specific length in B1 then use this formula in C1


=INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2!$A$2:$A$10)<B1),MATCH(A1,She et2!$B$1:$J$1)+(LOOKUP(A1,Sheet2!$B$1:$J$1)<A1))



"jimE" wrote:

I have a work sheet with prices on it. Across the top are widths down the
side is length.
24 30 36 ............................120
24 $1.00 $2.00 $3.33

30 2.20 3.20 4.20
.
.
120

On another work sheet i have a width and length cells when i enter sizes i
want it to return price. EG width might be 26 and length maybe 27 i want to
return price of 3.20. does anyone know how to do this. i new to excel so keep
it simply please if possible

Lookup price formula
 

Ok that worked sweet. thanks u r the daddy.

what would happen if i had 6 different price tables in the same format as
the one shown.
In sht one i want another col to state whate price table to look at like
table A,B,C....... so i put in width and lenght + price group this might be
different for each row. can this be done or am i just a pain in the B..T

cheers jimE

"daddylonglegs" wrote:

Hello Jim,

Looks like you want to round up to the next width or length shown, try this

If your table is in sheet2 with widths in B1:J1 and lengths in A2:A10 and
prices in B2:J10 then in sheet1 with a specific width in A1 (e.g. 26 in your
example) and a specific length in B1 then use this formula in C1


=INDEX(Sheet2!$B$2:$J$10,MATCH(B1,Sheet2!$A$2:$A$1 0)+(LOOKUP(B1,Sheet2!$A$2:$A$10)<B1),MATCH(A1,She et2!$B$1:$J$1)+(LOOKUP(A1,Sheet2!$B$1:$J$1)<A1))



"jimE" wrote:

I have a work sheet with prices on it. Across the top are widths down the
side is length.
24 30 36 ............................120
24 $1.00 $2.00 $3.33

30 2.20 3.20 4.20
.
.
120

On another work sheet i have a width and length cells when i enter sizes i
want it to return price. EG width might be 26 and length maybe 27 i want to
return price of 3.20. does anyone know how to do this. i new to excel so keep
it simply please if possible