The file is attached to the following link.
http://www.freefilehosting.net/download/3g6l9


thank you

"T. Valko" wrote:

Can you post another sample to refresh my memory?

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
Hi again,

is it possible to have a closest match value instead of "less than or
equal
to" in the formula?
thanks.

"T. Valko" wrote:

In both formulas, you're including the the top row and left column as
part
of the data table. The top row and left column are not part of the data
table

The data table is the range B2:D11.

So, the correct formulas should be (array entered):

B13:

=INDEX(B1:D1,MAX((B2:D11=MAX(IF(B2:D11<=A13,B2:D11 )))*(COLUMN(B1:D1)-MIN(COLUMN(B1:D1))+1)))

C13:

=INDEX(A2:A11,MAX((B2:D11=MAX(IF(B2:D11<=A13,B2:D1 1)))*(ROW(A2:A11)-MIN(ROW(A2:A11))+1)))

And the correct results a 16......1 5/8

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
hi again,
the formula is great, though, it seems to have a problem on smaller
numbers.
i have attached an example in the following link. as you can see in the
example, lookup values for 14 and 15 don't work. this is a sample from
a
large spreadsheet. also, this formula works on all values except for
the
numbers under 20 and only sometimes (the smalles lookup value is 7.08).
can
you help?

http://www.freefilehosting.net/download/3f41l

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

xLookup.xls 14kb

http://www.freefilehosting.net/download/3em0m

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
A1 is emtpy, the rest are full of values. is that what you were
referring
to
in your first reply?
to simplify, these are the numbers from the beginning of the
document.
top
row is in feets the left column is inches. what i'm getting at is
that
by
typing a number which is similar to anyone from the array, i want
the
formula
to find the less than or equal to value from the following chart and
return
feet and inch coordinates in two different cells.
3.00 4.00 5.00 6.00 7.00
0 3,206.05 2964.89 2722.28 2479.50 2236.71
1/8 3,203.53 2962.38 2719.75 2476.97 2234.18
1/4 3,201.02 2959.87 2717.22 2474.44 2231.65
3/8 3,198.51 2957.36 2714.69 2471.91 2229.12
1/2 3,196.00 2954.85 2712.16 2469.38 2226.60
5/8 3,193.49 2952.33 2709.63 2466.85 2224.07
3/4 3,190.97 2949.82 2707.11 2464.32 2221.54
7/8 3,188.46 2947.31 2704.58 2461.79 2219.01


"T. Valko" wrote:

So you want the closest match that is *less than or equal to* the
lookup_value?

The top row of values doesn't have the same number of entries as
the
other
rows. Does that mean the top leftmost field is empty?

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
hi Again,

the formula was great, however, it only works for the exact
numbers
in
the
table. A10, does not always contain the exact value from the
table
and
I
want
it to look for the closer number. this is what my actual range
looks
like

4.00 5.00 6.00 7.00 8.00 9.00
3,206.05 2964.89 2722.28 2479.50 2236.71 1993.93 1751.14
3,203.53 2962.38 2719.75 2476.97 2234.18 1991.40 1748.61
3,201.02 2959.87 2717.22 2474.44 2231.65 1988.87 1746.09

When i type 2960.50, it should match 2959.87, since it is closest
to
2960.50
and return corresponding values from the toprow and leftmost
column.

your help is immensely appreciated!!!
thanks
karl

"T. Valko" wrote:

Try this...

With you data in the range A1:G4...

A10 = lookup_value = 19

Array formulas** :

For the topmost:

=INDEX(A1:G1,MAX((A1:G4=A10)*COLUMN(A1:G4)-MIN(COLUMN(A1:G4))+1))

For the leftmost:

=INDEX(A1:A4,MAX((A1:G4=A10)*ROW(A1:G4)-MIN(ROW(A1:G4))+1))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

Your table seems a little odd to me. Usually the top leftmost
field
is
empty. Based on your table, if you enter 1 in A10 both formulas
will
also
return 1.

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
Hi,

In one cell I need to return topmost corresponding horizontal
value
of
a
table using a value from the table array.
In another cell, I need to return left most corresponding
vertical
value
in
a table using a value from the table array. example

1 2 3 4 5 6 7
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29

in one cell by typing 19, I want the formula to return 4
(matching
from
topmost row), in another cell 16 (matching from leftmost
column).

Thank you!
k