Return horizontal and vertical values
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
|