Index & match help

Hello,

I am looking what I believe to be a formula with index and match, but cant figure it out. What I am looking to do is create a spreadsheet for my window business that can look up prices for me instantly

I have 4 columns: EXAMPLE

window type ; height ; width ; price

SH ; 24 ; 36 ; $100
SH ; 24 ; 50 ; $200
SH ; 30 ; 36 ; $150
SH ; 30 ; 50 ; $250

i need a formula that compare the window type, the height and width and then spit out the price. the problem is i need the height and width to be variable, meaning if a window is 26 x 36 it will give me the 30 x 50 price ; if its 24 x 38 it will give me the 24 x 50 price...obviously i will have more window types than SH so I need that to be exact match and the height and width to be ranges and spit out the price i need. Can someone help me with this formula?

Thanks in advance!

(How to) Upload a Workbook directly to the Forum

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Hi, welcome to the forum

Agree that a sample workbook will help.

Have you considered creating a few tables? 1 for each "type" (SH, DH, etc), and have the width across the top and the length down the side? We could probably use an INDEX./MATCH fubction for this

I converted your table to this...

	F	G	H
10		WIDTH
11	HEIGHT	36	50
12	24	110	140
13	30	210	240
14	36	310	340

Then used this to pull the value from that table...
=INDEX($G$12:$H$14,MATCH(B3,F12:F14)+IF(INDEX($F$12:$F$14,MATCH(B3,F12:F14))=B3,0,1),MATCH(C3,G11:H11)+IF(INDEX($G$11:$H$11,MATCH(C3,G11:H11))=C3,0,1))

Now, I notice that the difference between SH and SS is 10...except for 30x60 - that diff is 20. was this a mistake? If so, then you could ajust my formula to this...
=INDEX($G$12:$H$14,MATCH(B3,F12:F14)+IF(INDEX($F$12:$F$14,MATCH(B3,F12:F14))=B3,0,1),MATCH(C3,G11:H11)+IF(INDEX($G$11:$H$11,MATCH(C3,G11:H11))=C3,0,1))+IF(A3="SS",10,0)

If you have more than those 2 tyupes, mayber we can work out a "difference" table for the extra?

Please post a more representative sample

You have lots of different sizes, OK, but surely they all still fall within certain sizes - so you could still build that table and expand on it. Also, I asked if there was any factor or amount that was a standard add-on for the various types? Perhaps have the SH (or whichever) as the base style, and all else is an increase on that - either amount of %...this could also be a small table that gets looked up.

Agree with Ben though, more detail will let us provide better suggestions

What if round up of height is available but round up of width is not?
i.e SH 25x55

Also, for each TYPE, height is in increasing order always?

I still think a series of tables like this will work for you. You could give each table a range name, and use that in the index/match

	F	G	H	I	J
11		24	36	50	60
12	24	100	125	150	NA
13	30	125	175	225	NA
14	36	150	200	250	NA
15	50	300	NA	NA	300

Give it a first shot, hope it is only shot.

=INDEX($D$12:$D$41,MATCH(1,($A$12:$A$41=$A$3)*($B$12:$B$41-$B$3=MIN(IF($B$12:$B$41-B3>=0,$B$12:$B$41-B3,"")))*($B$12:$B$41-B3>=0)*($C$12:$C$41-$C$3>=0),0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Originally Posted by BMBme

FDibbins, but a table is only two-way, i still need the type in there as well...

Thats where a table for each type comes in, with each 1 given a range name

Ohh, my mistake, try again:

=INDEX($D$12:$D$41,MATCH(1,($B$12:$B$41-$B$3=MIN(IF(($A$12:$A$41=$A$3)*($B$12:$B$41-$B$3>=0),$B$12:$B$41-$B$3,"")))*($B$12:$B$41-$B$3>=0)*($C$12:$C$41-$C$3>=0),0))

Try again:

=INDEX($D$12:$D$41,MATCH(1,($A$12:$A$41=$A$3)*($B$12:$B$41-$B$3=MIN(IF(($A$12:$A$41=$A$3)*($B$12:$B$41-$B$3>=0),$B$12:$B$41-$B$3,"")))*($B$12:$B$41-$B$3>=0)*($C$12:$C$41-$C$3>=0),0))

Hope it works!

Nice to hear it works.