Find a value in a table

[Colmans]

Hi

I'm trying to work out some code so I can select a value in a table based on two factors. The X & Y axis are numeric and range "up to" and I can reference both in cell in a form, and then calculate the appropriate factor.

	1000​	5000​	10000​
10​	0.0%​	0.5%​	0.8%​
20​	0.5%​	0.8%​	1.0%​
30​	0.8%​	1.0%​	1.5%​

Thanks in advance for any suggestions.

 

[etaf]

will the values be exact match to the x & y
or nearest and if nearest do you take the higher or lower value

grid lookup

=INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0))

Book14
	A	B	C	D	E	F	G	H	I	J
1		1000	5000	1000
2	10	0.0%	0.5%	0.8%			Y	20		0.8%
3	20	0.5%	0.8%	1.0%			X	5000
4	30	0.8%	1.0%	1.5%
Sheet1

Cell Formulas
Range	Formula
J2	J2	=INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0))

this explains a grid look where there is an exact match

Two-Way Lookups in Excel

mayneed to change the 0 to a 1 or -1 in the match parts
Or use a sumproduct
depends on answers as to nearest ,

 

[Colmans]

will the values be exact match to the x & y
or nearest and if nearest do you take the higher or lower value

grid lookup

=INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0))

Book14
	A	B	C	D	E	F	G	H	I	J
1		1000	5000	1000
2	10	0.0%	0.5%	0.8%			Y	20		0.8%
3	20	0.5%	0.8%	1.0%			X	5000
4	30	0.8%	1.0%	1.5%
Sheet1

Cell Formulas
Range	Formula
J2	J2	=INDEX(B2:D4,MATCH(H2,A2:A4,0),MATCH(H3,B1:D1,0))

this explains a grid look where there is an exact match

Two-Way Lookups in Excel

mayneed to change the 0 to a 1 or -1 in the match parts
Or use a sumproduct
depends on answers as to nearest ,

Thank you - will see how I get on.

 

[etaf]

ok,
as i said - this is for an exact value match
not a close match

so if you had
Y = 22
X = 1129

what result would you expect and why
ALSO

What version of excel are you using - would be worth updating your profile to show version as solutions will be dependant on what version you have and therefore what functions available.
Excel Versions are: 2003/2007/2010/2013/2016/2019/2021/2024 (due April24) /365 subscription , also rather than show more than 1 version . if you have them , show which version you will be using the solution for.
An awful lot of new functions have now been added to the newer versions