Price Lookup

[Guest]

Hello, i want to lookup a certain price of a door in a price matrix that
matches criteria both in the left most column and topmost row that is between
2 values (for height) and 2 values (for width). So eg. i want to find the
price of a door which is 720 high X 450 Wide. I have to retrieve the price
from the matrix that the door sits in. So for left most column it maybe
between sizes 650-830High and 380-490wide, where 650-830 sits in one cell.
Any help appreciated. if i haven't explained myself properly or you need
further information please ask.

cheers

 

[T. Valko]

You need to restructure your table.

Make it so that both the vertical and the horizontal headers are the lower
boundary of each size interval.

See this screencap:

http://img252.imageshack.us/img252/6260/lookupny4.jpg

Biff

 

[Guest]

Oops, try this
http://www.findarticles.com/p/articles/mi_zdpcm/is_200305/ai_ziff40152

 

[Guest]

Try this

 

[T. Valko]

Are you sure about that link?

I get redirected!

Biff

 

[Guest]

101 - 150 151 - 250 251 - 300 301 - 350
125 - 230 $18.32 $19.97 $20.05 $20.56
231 - 350 $20.90 $23.29 $23.61 $24.63
351 - 450 $22.85 $25.86 $27.56 $29.35
451 - 550 $24.55 $28.27 $32.47 $34.56
551 - 650 $24.80 $30.61 $34.91 $36.95
651 - 800 $24.88 $33.83 $37.04 $38.87
801 - 950 $27.29 $37.05 $41.58 $43.43

this is how the matrix looks

 

[T. Valko]

Make it like this:

http://img388.imageshack.us/img388/1471/lookup2cx1.jpg

Biff

 

[Guest]

thanks for your help so far. i have changed headers to suit. but the i think
the link shows me for a "match". What command do i use so it keeps going down
the row until it finds a number no bigger than the door width and then
accross the columns to find a number no bigger than the width?

 

[Guest]

sorry i thought id better post this. i hope this explains it a bit better. i
need the top matrix to be filled out by looking at the bottom matrix
according to sizes(topmost leftmost column/row).

240 301
240 $- $- $-
301 $- $- $-
421 $- $- $-
541 $- $- $-
661 $- $- $-

251 301 351
351 $15.00 $20.05 $20.56
451 $20.00 $23.61 $24.63
551 $21.00 $27.56 $29.35
651 $25.00 $32.47 $34.56
801 $30.00 $34.91 $36.95

 

[T. Valko]

I'm not following you.

Biff

sorry i thought id better post this. i hope this explains it a bit better.
i
need the top matrix to be filled out by looking at the bottom matrix
according to sizes(topmost leftmost column/row).

240 301
240 $- $- $-
301 $- $- $-
421 $- $- $-
541 $- $- $-
661 $- $- $-

251 301 351
351 $15.00 $20.05 $20.56
451 $20.00 $23.61 $24.63
551 $21.00 $27.56 $29.35
651 $25.00 $32.47 $34.56
801 $30.00 $34.91 $36.95

 

[Guest]

Well i need the top chart to have a function to look up the bottom chart
according to hieght and width. so if a door is 500H x 388W. topchart needs to
have a formula at in Column3 Row3 that reads the botChart Column4 Row3.
topmost is width and leftmost is Hieght of a specified door. so if i change
either topmost or leftmost numbers then the price on topchart should look up
bottom chart and fill cell with a different price.

240 301 421
240 $- $- $-
301 $- $- $-
421 $- $- $-
541 $- $- $-
661 $- $- $-

251 301 351 380
351 $15.00 $20.05 $20.56 $20.80
451 $20.00 $23.61 $24.63 $25.20
551 $21.00 $27.56 $29.35 $30.54
651 $25.00 $32.47 $34.56 $35.76
801 $30.00 $34.91 $36.95 $37.32

 

[T. Valko]

Let me see if I get this.

This is your top chart:

................240.....301.....421
240....................................
301....................................
421..............................XX
541....................................
661....................................

You say: topchart needs to have a formula at in Column3 Row3 that reads the
botChart Column4 Row3.

I have C3R3 in the top chart marked with the XX

C4R3 of the bottom chart is 551 - 380

How does that relate to the top chart C3R3 which is 421 - 421 ?

I have to tell you that I'm not following this at all.

Maybe someone else can figure it out!

Biff

 

[Guest]

i know it sounds confusing. the price that should go in topchart is based on
2 variables. Height and Width of a door. where the height is C1 and Width is
R1. you can see this by numbers 240 301 421.... Now if a door is 500 high and
380 wide i want to be able to read the price from topchart, so you are right
where XX is. XX now needs to read bottomchart by going down C1 until it gets
to a Column no more than 500 in height then across rows till it gets to no
further than a 380 in width. thats pretty much it. now if i change in top
chart C1R4 to any other number apart from 421 it will then read the bot chart
accordingly. does that make sense biff.?

 

[T. Valko]

Sorry, I'm just not following you on this.

If you don't get any other replies try reposting and hopefully a fresh set
of eyes might see what you want.

Biff