Indexing price tables

[Blind_Man]

Hi,
I'm having some trouble trying to pick the correct cell on a price table.
The price tables are for window coverings (blinds etc) and the price I need to select is based on a row of widths and a column of heights.
I've tried using INDEX,MATCH,MATCH and VLOOKUP but can't get it to select the correct option. so hopefully someone can help!

So in this example if I enter the width as 900 (at A3), and the height as 1550 (at B3), I need it to return 102 (at C3)

I can get it to kind of work, but it either selects the value lower than what I need, or the highest value in the table, depending on whether I have 1 or -1 in the MATCH function.

What would be the best way to achieve this?

Thanks!

 

[etaf]

IGNORE DOES NOT WORK CORRECTLY - when value matches

try
=INDEX(G3:K7,MATCH(B3,F3:F7)+1,MATCH(A3,G2:K2)+1)

works on an example i already have

Grid lookup Nearest value.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2							2000	5000	10000	15000	20000	25000	30000	40000
3	5400	17		0.185		8	0.3	0.235	0.17	0.14	0.12	0.11	0.1	0.9
4						12	0.305	0.24	0.175	0.145	0.125	0.115	0.105	0.95
5						16	0.31	0.245	0.18	0.15	0.13	0.12	0.11	0.1
6						20	0.315	0.25	0.185	0.155	0.135	0.125	0.115	0.105
7
Sheet3

Cell Formulas
Range	Formula
D3	D3	=INDEX(G3:K7,MATCH(B3,F3:F7)+1,MATCH(A3,G2:K2)+1)

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[Kamolga]

At the end of match formula, we can select less than, equal or greater than. Since greater than requires the data to be sorted descending, I used less than+1. Hope it helps

 

[etaf]

try
=INDEX(G3:N6,MATCH(MIN(IF(F3:F6>=B3,F3:F6)),F3:F6,1),MATCH(MIN(IF(G2:N2>=A3,G2:N2)),G2:N2,1))

see various examples in row D3 to D6

Grid lookup Nearest value.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2							650	850	1050	1250	1450
3	900	1550		0.185		1000	0.3	0.235	0.17	0.14	0.12
4	850	1550		0.25		1200	0.305	0.24	0.175	0.145	0.125
5	1250	1200		0.145		1400	0.31	0.245	0.18	0.15	0.13
6	1375	1250		0.13		1600	0.315	0.25	0.185	0.155	0.135
7						1800	0.32	0.255	0.19	0.16	0.14
Sheet3

Cell Formulas
Range	Formula
D3	D3	=INDEX(G3:N6,MATCH(MIN(IF(F3:F6>=B3,F3:F6)),F3:F6,1),MATCH(MIN(IF(G2:N2>=A3,G2:N2)),G2:N2,1))
D4	D4	=INDEX(G3:K7,MATCH(MIN(IF(F3:F7>=B4,F3:F7)),F3:F7,1),MATCH(MIN(IF(G2:K2>=A4,G2:K2)),G2:K2,1))
D5	D5	=INDEX(G3:K7,MATCH(MIN(IF(F3:F7>=B5,F3:F7)),F3:F7,1),MATCH(MIN(IF(G2:K2>=A5,G2:K2)),G2:K2,1))
D6	D6	=INDEX(G3:K7,MATCH(MIN(IF(F3:F7>=B6,F3:F7)),F3:F7,1),MATCH(MIN(IF(G2:K2>=A6,G2:K2)),G2:K2,1))

 

[Blind_Man]

Thanks so much for the replies! It works perfectly now.
I knew it was referencing the next lowest size to what I wanted, but couldn't think of how to get it to choose the next one up. These solutions did that.

Thanks again!

 

[etaf]

you are welcome