Look up table

[Howard Brazee]

I figure there's a direct way of doing a table look up in Excel, seeing Excel is
based upon tables.

But I am not finding it.

I have a table with ranges for each column and each row. Here's part of it:

2 3 4
2 3 4
3.0 3.4 0.0 0.0 0.0
3.5 3.9 0.0 0.0 0.0
4.0 4.4 1.0 0.0 0.0
4.5 4.9 1.8 1.0 0.0
5.0 5.4 2.6 1.9 1.0
5.5 5.9 3.4 2.7 1.9
6.0 6.4 4.1 3.5 2.8




When X = 5.3 and y = 2.0, I want to return a value of 2.6.

How should I go about this?

 

[Frank Kabel]

Hi
try
=INDEX(A1:F20,MATCH(5.3,A1:A20,1),MATCH(2.0,A1:F1,1))

 

[Howard Brazee]

Hi
try
=INDEX(A1:F20,MATCH(5.3,A1:A20,1),MATCH(2.0,A1:F1,1))

Boy that probably is right, but looking at it and the INDEX help, I am not quite
getting it.

Let me describe my table exactly:

$F$7:$F$28 is a column containing the smallest number in range 1
$G$7:$G$28 is a column containing the largest number in range 1.

$H$5:$O$5 is a row containing the smallest number in range 2
$H$6:$G:6 is a row containing the largest number in range 2

$F$2 contains the number I am using to look up in range 2.
$E$4 contains the number I am looking up in range 1.

The data are in $H$7:$O$29

So if $E$2 contains the number 11.3, I will want to find row 23 which has 11.0
in $F$23 and 11.4 in $F$23
And if $E$4 contains the number 4, I want to find row J which has 4 in $J$5 and
4 in $J$6.

Now that I have row and column, I want to retrieve the number 10.2 which is in
$J$23.

 

[Frank Kabel]

Hi
try
INDEX(F5:O29,MATCH(E2,F5:F29,1),MATCH(F2,F5:O5,1))

 

[Howard Brazee]

INDEX(F5:O29,MATCH(E2,F5:F29,1),MATCH(F2,F5:O5,1))

Tested it out. It works fine (I should have had E4 instead of E2 above).
Only trouble is sometimes my numbers are out of range and return a #N/A.

Can I change this behavior?

Alternately, I can add an extra row or column to handle extreme cases.

 

[Frank Kabel]

Hi
and what should the formula return in these cases?. a blank cell?. If
yes try
=IF(ISNA(INDEX(...)),"",INDEX(...))

--
Regards
Frank Kabel
Frankfurt, Germany

Tested it out. It works fine (I should have had E4 instead of E2 above).
Only trouble is sometimes my numbers are out of range and return a #N/A.

Can I change this behavior?

Alternately, I can add an extra row or column to handle extreme

cases.

 

[Howard Brazee]

Hi
and what should the formula return in these cases?. a blank cell?. If
yes try
=IF(ISNA(INDEX(...)),"",INDEX(...))

Great function - I can use it several places.

Thanks.