Can you please help me? I am trying to figure out how to do a lookup
with this data.

Track Width 50 75 100 125 150
drop upto 60" 60.01 73.64 98.2 122.72 147.3
drop upto 80" 73.35 87.52 116.7 145.87 175.05
drop upto 100" 83.6 101.37 135.2 168.99 202.8
drop upto 120" 94.85 112.27 149.7 187.12 224.55


Enter Details
*Width*
*Drop*

Price


If you were to add a value into the bold boxes (width and drop) I want
a formula which looks up
the relevant price.
for Example if you were to type 65 in the width box and 72 in drop I
would like C16 to come up with the price in C3.
I'm struggling to work out the function I need.

Many Thanks


--
oldruts
------------------------------------------------------------------------
oldruts's Profile: http://www.excelforum.com/member.php...o&userid=37262
View this thread: http://www.excelforum.com/showthread...hreadid=569787

anybody help please?


--
oldruts
------------------------------------------------------------------------
oldruts's Profile: http://www.excelforum.com/member.php...o&userid=37262
View this thread: http://www.excelforum.com/showthread...hreadid=569787

add a new left column to your table A, B and C, ie any drop up to 60 is
coded A, 60.01 to 80 is coded B and anything above 80 is coded C

Now create droptable as below

1......A
60....A
60.01...B
80.....B
80.01...C
999...C

when you input the drop ie 65

=vlookup(65,droptable,2)

this returns B

do the same for widths, using P,Q,R,S T for the codes (adding new row
at the top)

Now you can use 2 match functions within an offset function

..........P.....Q......R.......S.......T
A
B..............87.52............
C


ASSUMING THE "P" IS IN CELL A2

=OFFSET($A$2,MATCH(C1,ABCNAME,0),MATCH(A3,PQRSTNAM E,0))

where abcname is the range B1:D1 and pqrstname is range A2:E2


this returns 87.52


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=569787

thanks Robert I will give it a whirl


--
oldruts
------------------------------------------------------------------------
oldruts's Profile: http://www.excelforum.com/member.php...o&userid=37262
View this thread: http://www.excelforum.com/showthread...hreadid=569787

thanks Robert I will give it a whirl


--
oldruts
------------------------------------------------------------------------
oldruts's Profile: http://www.excelforum.com/member.php...o&userid=37262
View this thread: http://www.excelforum.com/showthread...hreadid=569787

Would this work for you?

If you have your data in a1 to f5 and change your text of "drop up
to60" etc" to just a number e.g. 60

Then if you can enter the Width in C13 and the drop in c14

You can then have the formula
=IF(OR(C13="",C14=""),"",HLOOKUP(C13,B1:F5,IF(C14< (A2+1),2,IF(C14<(A3+1),3,IF(C14<(A4+1),4,5)))))
to find the price?

Does that work for you?

Regards

Carl

oldruts Wrote:
Can you please help me? I am trying to figure out how to do a lookup
with this data.

Track Width 50 75 100 125 150
drop upto 60" 60.01 73.64 98.2 122.72 147.3
drop upto 80" 73.35 87.52 116.7 145.87 175.05
drop upto 100" 83.6 101.37 135.2 168.99 202.8
drop upto 120" 94.85 112.27 149.7 187.12 224.55


Enter Details
*Width*
*Drop*

Price


If you were to add a value into the bold boxes (width and drop) I want
a formula which looks up
the relevant price.
for Example if you were to type 65 in the width box and 72 in drop I
would like C16 to come up with the price in C3.
I'm struggling to work out the function I need.

Many Thanks


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=569787

I have managed to get the formulas working, and it is exactly how I want
it to look in excel. However, when I publish it as a web page it shows
this

http://www.phoenixconstructiongbltd.co.uk/bathroom.htm

I want to make use of the calculator but I dont want it to look like a
screen shot from excel I want it to look like a form. Something like
below

http://www.hutchal.clara.net/curtain...-estimator.htm

Any advice greatly appreciated


--
oldruts
------------------------------------------------------------------------
oldruts's Profile: http://www.excelforum.com/member.php...o&userid=37262
View this thread: http://www.excelforum.com/showthread...hreadid=569787