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

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

Hi T. Valko,
Sorry to jump in, but I've tried your formulas, and they work perfectly (of
course).
But I can't follow the logic. Could you please expound?
Dave.

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

Although the formula I posted will return the correct result I made a very
slight change in the logical precedence in how it calculates:

=INDEX(A1:C1,MAX((A1:C3=A10)*COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))
=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))

Let's use this smaller table to see how it works:

......A...B...C
1...1...2...3
2...4...5...6
3...7...8...9

Lookup_value = 6

For the topmost:

=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))

=INDEX(A1:C1

The indexed array is A1:C1. Each element of the array is in a specific
indexed position. A1 is in position 1, B1 is in position 2 and C1 is in
position 3. What we need to do to get the result we're after is tell INDEX
we want the value located at position N of the indexed array. We do that by
calculating this:

MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))

This logical expression will return an array of either TRUE or FALSE:

(A1:C3=A10)

T = TRUE
F = FALSE

A1=6=F;B1=6=F;C1=6=F
A2=6=F;B2=6=F;C2=6=T
A3=6=F;B3=6=F;C3=6=F

These TRUE and FALSE are then multiplied by the column numbers that make up
the table range A:C = columns 1,2,3:

(A1:C3=A10)*(COLUMN(A1:C3))

{F,F,F} * {1,2,3}
{F,F,T} * {1,2,3}
{F,F,F} * {1,2,3}

TRUE multiplied by any number other than 0 = that number
FALSE multiplied by any number = 0

So:

{F,F,F} * {1,2,3}= 0,0,0
{F,F,T} * {1,2,3}= 0,0,3
{F,F,F} * {1,2,3}= 0,0,0

This array is then passed to the MAX function:

MAX({0,0,0;0,0,3;0,0,0}) = 3

The result of MAX is then passed to INDEX and the result of the formula is
the value held in position 3 of the indexed array A1:C1:

=INDEX(A1:C1,3) = C1 = 3

Now comes the confusing part!

The positions of the indexed array are *relative* to the referenced range.
If the indexed array was G27:I27 their *relative* positions would still be
G27 in position 1, H27 in position 2 and I27 in position 3. Where this
matters is in this expression:

These TRUE and FALSE are then multiplied by the column numbers

To make sure we end up with *relative* positions that we can pass to the
INDEX function we have to calculate any offset in the range references.

These TRUE and FALSE are then multiplied by the column numbers

If the table range was G27:I29 their column numbers a

G = 7
H = 8
I = 9

When these column numbers are multiplied along with the TRUE and FALSE of
the logical expression then we would end up with numbers that do not
coincide with the *relative* positions of the indexed array. So, we need to
convert 7,8,9 to 1,2,3. This is how we do that:

-MIN(COLUMN(G27:I29))+1

COLUMN(G27:I29)-MIN(COLUMN(G27:I29))+1

G = col 7 - 7 = 0 + 1 = 1
H = col 8 - 7 = 1 + 1 = 2
I = col 9 - 7 = 2 + 1 = 3

Now our calculated positions coincide with the positions of the indexed
array.

This same logic applies to to formula for the leftmost as well.

NB: This is also a way to make the formula robust against column/row
insertions.


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi T. Valko,
Sorry to jump in, but I've tried your formulas, and they work perfectly
(of
course).
But I can't follow the logic. Could you please expound?
Dave.

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

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

Hi Bif,
Thankyou for taking the time and effort to reply. I love using functions and
formulas, and finding different uses for them (a bit sad to some non-XL-ers,
but I don't care). But I am new to array functions and formulas, and am just
picking them up as I read questions and answers from this group. Judging by
clarity of your reply, you are a teacher of this stuff - or you should be.
Regards - Dave.

"T. Valko" wrote:

Although the formula I posted will return the correct result I made a very
slight change in the logical precedence in how it calculates:

=INDEX(A1:C1,MAX((A1:C3=A10)*COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))
=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))

Let's use this smaller table to see how it works:

......A...B...C
1...1...2...3
2...4...5...6
3...7...8...9

Lookup_value = 6

For the topmost:

=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))

=INDEX(A1:C1

The indexed array is A1:C1. Each element of the array is in a specific
indexed position. A1 is in position 1, B1 is in position 2 and C1 is in
position 3. What we need to do to get the result we're after is tell INDEX
we want the value located at position N of the indexed array. We do that by
calculating this:

MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))

This logical expression will return an array of either TRUE or FALSE:

(A1:C3=A10)

T = TRUE
F = FALSE

A1=6=F;B1=6=F;C1=6=F
A2=6=F;B2=6=F;C2=6=T
A3=6=F;B3=6=F;C3=6=F

These TRUE and FALSE are then multiplied by the column numbers that make up
the table range A:C = columns 1,2,3:

(A1:C3=A10)*(COLUMN(A1:C3))

{F,F,F} * {1,2,3}
{F,F,T} * {1,2,3}
{F,F,F} * {1,2,3}

TRUE multiplied by any number other than 0 = that number
FALSE multiplied by any number = 0

So:

{F,F,F} * {1,2,3}= 0,0,0
{F,F,T} * {1,2,3}= 0,0,3
{F,F,F} * {1,2,3}= 0,0,0

This array is then passed to the MAX function:

MAX({0,0,0;0,0,3;0,0,0}) = 3

The result of MAX is then passed to INDEX and the result of the formula is
the value held in position 3 of the indexed array A1:C1:

=INDEX(A1:C1,3) = C1 = 3

Now comes the confusing part!

The positions of the indexed array are *relative* to the referenced range.
If the indexed array was G27:I27 their *relative* positions would still be
G27 in position 1, H27 in position 2 and I27 in position 3. Where this
matters is in this expression:

These TRUE and FALSE are then multiplied by the column numbers

To make sure we end up with *relative* positions that we can pass to the
INDEX function we have to calculate any offset in the range references.

These TRUE and FALSE are then multiplied by the column numbers

If the table range was G27:I29 their column numbers a

G = 7
H = 8
I = 9

When these column numbers are multiplied along with the TRUE and FALSE of
the logical expression then we would end up with numbers that do not
coincide with the *relative* positions of the indexed array. So, we need to
convert 7,8,9 to 1,2,3. This is how we do that:

-MIN(COLUMN(G27:I29))+1

COLUMN(G27:I29)-MIN(COLUMN(G27:I29))+1

G = col 7 - 7 = 0 + 1 = 1
H = col 8 - 7 = 1 + 1 = 2
I = col 9 - 7 = 2 + 1 = 3

Now our calculated positions coincide with the positions of the indexed
array.

This same logic applies to to formula for the leftmost as well.

NB: This is also a way to make the formula robust against column/row
insertions.


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi T. Valko,
Sorry to jump in, but I've tried your formulas, and they work perfectly
(of
course).
But I can't follow the logic. Could you please expound?
Dave.

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

You're welcome. Thanks for the feedback!


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Bif,
Thankyou for taking the time and effort to reply. I love using functions
and
formulas, and finding different uses for them (a bit sad to some
non-XL-ers,
but I don't care). But I am new to array functions and formulas, and am
just
picking them up as I read questions and answers from this group. Judging
by
clarity of your reply, you are a teacher of this stuff - or you should be.
Regards - Dave.

"T. Valko" wrote:

Although the formula I posted will return the correct result I made a
very
slight change in the logical precedence in how it calculates:

=INDEX(A1:C1,MAX((A1:C3=A10)*COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))
=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))

Let's use this smaller table to see how it works:

......A...B...C
1...1...2...3
2...4...5...6
3...7...8...9

Lookup_value = 6

For the topmost:

=INDEX(A1:C1,MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1)))

=INDEX(A1:C1

The indexed array is A1:C1. Each element of the array is in a specific
indexed position. A1 is in position 1, B1 is in position 2 and C1 is in
position 3. What we need to do to get the result we're after is tell
INDEX
we want the value located at position N of the indexed array. We do that
by
calculating this:

MAX((A1:C3=A10)*(COLUMN(A1:C3)-MIN(COLUMN(A1:C3))+1))

This logical expression will return an array of either TRUE or FALSE:

(A1:C3=A10)

T = TRUE
F = FALSE

A1=6=F;B1=6=F;C1=6=F
A2=6=F;B2=6=F;C2=6=T
A3=6=F;B3=6=F;C3=6=F

These TRUE and FALSE are then multiplied by the column numbers that make
up
the table range A:C = columns 1,2,3:

(A1:C3=A10)*(COLUMN(A1:C3))

{F,F,F} * {1,2,3}
{F,F,T} * {1,2,3}
{F,F,F} * {1,2,3}

TRUE multiplied by any number other than 0 = that number
FALSE multiplied by any number = 0

So:

{F,F,F} * {1,2,3}= 0,0,0
{F,F,T} * {1,2,3}= 0,0,3
{F,F,F} * {1,2,3}= 0,0,0

This array is then passed to the MAX function:

MAX({0,0,0;0,0,3;0,0,0}) = 3

The result of MAX is then passed to INDEX and the result of the formula
is
the value held in position 3 of the indexed array A1:C1:

=INDEX(A1:C1,3) = C1 = 3

Now comes the confusing part!

The positions of the indexed array are *relative* to the referenced
range.
If the indexed array was G27:I27 their *relative* positions would still
be
G27 in position 1, H27 in position 2 and I27 in position 3. Where this
matters is in this expression:

These TRUE and FALSE are then multiplied by the column numbers

To make sure we end up with *relative* positions that we can pass to the
INDEX function we have to calculate any offset in the range references.

These TRUE and FALSE are then multiplied by the column numbers

If the table range was G27:I29 their column numbers a

G = 7
H = 8
I = 9

When these column numbers are multiplied along with the TRUE and FALSE of
the logical expression then we would end up with numbers that do not
coincide with the *relative* positions of the indexed array. So, we need
to
convert 7,8,9 to 1,2,3. This is how we do that:

-MIN(COLUMN(G27:I29))+1

COLUMN(G27:I29)-MIN(COLUMN(G27:I29))+1

G = col 7 - 7 = 0 + 1 = 1
H = col 8 - 7 = 1 + 1 = 2
I = col 9 - 7 = 2 + 1 = 3

Now our calculated positions coincide with the positions of the indexed
array.

This same logic applies to to formula for the leftmost as well.

NB: This is also a way to make the formula robust against column/row
insertions.


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi T. Valko,
Sorry to jump in, but I've tried your formulas, and they work perfectly
(of
course).
But I can't follow the logic. Could you please expound?
Dave.

wonderful!
thank you.

"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

You're welcome!

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
wonderful!
thank you.

"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

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

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

thank you it works great.

"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

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

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

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

Enter this array formula** in B22:

=INDEX(B2:G2,MAX((ABS(B3:G19-A22)=MIN(ABS(B3:G19-A22)))*(COLUMN(B2:G2)-MIN(COLUMN(B2:G2))+1)))

Enter this array formula** in C22:

=INDEX(A3:A19,MAX((ABS(B3:G19-A22)=MIN(ABS(B3:G19-A22)))*(ROW(A3:A19)-MIN(ROW(A3:A19))+1)))

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

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
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

Thank you T.Valko!

"T. Valko" wrote:

Enter this array formula** in B22:

=INDEX(B2:G2,MAX((ABS(B3:G19-A22)=MIN(ABS(B3:G19-A22)))*(COLUMN(B2:G2)-MIN(COLUMN(B2:G2))+1)))

Enter this array formula** in C22:

=INDEX(A3:A19,MAX((ABS(B3:G19-A22)=MIN(ABS(B3:G19-A22)))*(ROW(A3:A19)-MIN(ROW(A3:A19))+1)))

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

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
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

You're welcome!

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
Thank you T.Valko!

"T. Valko" wrote:

Enter this array formula** in B22:

=INDEX(B2:G2,MAX((ABS(B3:G19-A22)=MIN(ABS(B3:G19-A22)))*(COLUMN(B2:G2)-MIN(COLUMN(B2:G2))+1)))

Enter this array formula** in C22:

=INDEX(A3:A19,MAX((ABS(B3:G19-A22)=MIN(ABS(B3:G19-A22)))*(ROW(A3:A19)-MIN(ROW(A3:A19))+1)))

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

--
Biff
Microsoft Excel MVP


"karl" wrote in message
...
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