 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2007Price Lookup
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 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 > 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 [quoted text clipped - 7 lines] > > cheers 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 > You need to restructure your table. > [quoted text clipped - 18 lines] > > > > cheers Make it like this: http://img388.imageshack.us/img388/1471/lookup2cx1.jpg Biff > 101 - 150 151 - 250 251 - 300 301 - 350 > 125 - 230 $18.32 $19.97 $20.05 $20.56 [quoted text clipped - 35 lines] >> > >> > cheers 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? > Make it like this: > [quoted text clipped - 41 lines] > >> > > >> > cheers 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 > Make it like this: > [quoted text clipped - 41 lines] > >> > > >> > cheers I'm not following you. Biff > sorry i thought id better post this. i hope this explains it a bit better. > i [quoted text clipped - 63 lines] >> >> > >> >> > cheers 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 > >> Make it like this: > >> [quoted text clipped - 44 lines] > >> >> > > >> >> > cheers 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 > 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 [quoted text clipped - 74 lines] >> >> >> > >> >> >> > cheers 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.? > Let me see if I get this. > [quoted text clipped - 100 lines] > >> >> >> > > >> >> >> > cheers 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 >i know it sounds confusing. the price that should go in topchart is based >on [quoted text clipped - 125 lines] >> >> >> >> > >> >> >> >> > cheers Oops, try this http://www.findarticles.com/p/articles/mi_zdpcm/is_200305/ai_ziff40152 > 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 [quoted text clipped - 6 lines] > > cheers Are you sure about that link? I get redirected! Biff > Oops, try this > http://www.findarticles.com/p/articles/mi_zdpcm/is_200305/ai_ziff40152 [quoted text clipped - 12 lines] >> >> cheers Try this > 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 [quoted text clipped - 6 lines] > > cheers |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.