look up x and y axis

  • Thread starter Thread starter xtothaz
  • Start date Start date
X

xtothaz

hi,
i need some help i need a formula that looks up both x and y axis and
returns the value.

i am looking for a formula that put the exact size into the formula eg
120 x 650 and it looks up the ranges to find the price which would be
$18.15

spreadsheets is attached
please help

thanks


+----------------------------------------------------------------+
| Attachment filename: doors.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=349629|
+----------------------------------------------------------------+
 
With defined names "Width" and "Height" referring to the cells in which you
enter the desired width and height, use the following array formula:

=INDIRECT(CHAR(SUM(IF((Width>=LEFT(C1:N1,FIND("-",C1:N1)-1)+0)*(Width<=MID(C
1:N1,FIND("-",C1:N1)+1,10)+0),COLUMN(C1:N1),0))+64)&SUM(IF((Height>=LEFT(A2:
A11,FIND("-",A2:A11)-1)+0)*(Height<=MID(A2:A11,FIND("-",A2:A11)+1,10)+0),ROW
(A2:A11),0)))

Remember that you'll need to array-enter it. (Instead of hitting Enter
after typing in the formula, hit Ctrl-Shift-Enter.)

/i.
 
Hi xtothaz,

I didn't get you're attachment. They don't post to the web
interface.

If you created an index table with the X axis containing
the value 120 and the Y axis containing the value 650 and
the price value at the intersect, use a simple formula
like this:

=INDEX(D2:G10,MATCH(A1,C2:C10,0),MATCH(B1,D1:G1,0))

You can enter the sizes you want the price for in say A1
and B1. This provides more functionality. The X axis is
C2:C10 and Y axis D1:G1.

Biff
 
immanuel,

thanks alot for your help it works great. i cant thank you enough i
have been trying to figure it out for months now. once again thanks for
the help!!!!
 
Back
Top