Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Price Lookup | Excel Worksheet Functions | |||
Help: Need Excel formula to return correct price from price history table | Excel Discussion (Misc queries) | |||
Lookup Postage Price [min & max weight] | Excel Worksheet Functions | |||
create a formula for price * discount* tax =final price | Excel Discussion (Misc queries) | |||
formula for - price times discount times tax = final price | New Users to Excel |