How to code the formula?
 

Does anyone have any suggestions on how to code the formula within Excel?
There is a list of values under M2:M27 in descending order with space in
between, and show below
M N
1871 1866


1849 1830
1830 1825

1786 1781

1658 1657
1641 1641
1630

There is a given number 1780 in cell C37, I would like to determine the
matched value under N column, and return it into F37, which find the max.
number under column M and this number is less than the given number 1780, the
matched number should be 1658 under column M, and 1657 under column N should
be returned into cell F37.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to code the formula?
 

hey eric!

im no expert, but have you tried a LOOKUP function?

ie LOOKUP(lookup_Value,lookup_Vector,Result_Vector)
so, F37 = Lookup(C37, M2:M27,N2:N27)

the lookup will automatically search for the largest number equal to or less
than ur given value in C37.


"Eric" wrote:

Does anyone have any suggestions on how to code the formula within Excel?
There is a list of values under M2:M27 in descending order with space in
between, and show below
M N
1871 1866


1849 1830
1830 1825

1786 1781

1658 1657
1641 1641
1630

There is a given number 1780 in cell C37, I would like to determine the
matched value under N column, and return it into F37, which find the max.
number under column M and this number is less than the given number 1780, the
matched number should be 1658 under column M, and 1657 under column N should
be returned into cell F37.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to code the formula?
 

THank you very much for suggestions
I return nothing.
Are you sure LOOKUP function working for my case?
Thanks in advance for any suggestions
Eric

"Derrick" wrote:

hey eric!

im no expert, but have you tried a LOOKUP function?

ie LOOKUP(lookup_Value,lookup_Vector,Result_Vector)
so, F37 = Lookup(C37, M2:M27,N2:N27)

the lookup will automatically search for the largest number equal to or less
than ur given value in C37.


"Eric" wrote:

Does anyone have any suggestions on how to code the formula within Excel?
There is a list of values under M2:M27 in descending order with space in
between, and show below
M N
1871 1866


1849 1830
1830 1825

1786 1781

1658 1657
1641 1641
1630

There is a given number 1780 in cell C37, I would like to determine the
matched value under N column, and return it into F37, which find the max.
number under column M and this number is less than the given number 1780, the
matched number should be 1658 under column M, and 1657 under column N should
be returned into cell F37.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to code the formula?
 

hmm,
well, The lookup should look up the column M for the biggest equal to or
less than value you insert. then it should return the value in the N column
which would be in the same position if it were the M column. Make sense?

Make sure the Values in M, N line up horizontally

If that doesnt work, i dont know. Sorry
Use the Help (F1) from Excel, and search LOOKUP. it should give you enough
to solve this... Im pretty sure lookup is the function you want.

"Eric" wrote:

THank you very much for suggestions
I return nothing.
Are you sure LOOKUP function working for my case?
Thanks in advance for any suggestions
Eric

"Derrick" wrote:

hey eric!

im no expert, but have you tried a LOOKUP function?

ie LOOKUP(lookup_Value,lookup_Vector,Result_Vector)
so, F37 = Lookup(C37, M2:M27,N2:N27)

the lookup will automatically search for the largest number equal to or less
than ur given value in C37.


"Eric" wrote:

Does anyone have any suggestions on how to code the formula within Excel?
There is a list of values under M2:M27 in descending order with space in
between, and show below
M N
1871 1866


1849 1830
1830 1825

1786 1781

1658 1657
1641 1641
1630

There is a given number 1780 in cell C37, I would like to determine the
matched value under N column, and return it into F37, which find the max.
number under column M and this number is less than the given number 1780, the
matched number should be 1658 under column M, and 1657 under column N should
be returned into cell F37.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to code the formula?
 

Your complication is that your datalist is in descending order.

Try this *array* formula:

=INDEX(N2:N27,MATCH(1,(M2:M27<=C37)*(M2:M27<""),0 ))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.


I'm assuming a match in Column M will also be valid.
If not, remove the = sign from
<=C37
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Eric" wrote in message
...
Does anyone have any suggestions on how to code the formula within Excel?
There is a list of values under M2:M27 in descending order with space in
between, and show below
M N
1871 1866


1849 1830
1830 1825

1786 1781

1658 1657
1641 1641
1630

There is a given number 1780 in cell C37, I would like to determine the
matched value under N column, and return it into F37, which find the max.
number under column M and this number is less than the given number 1780,
the
matched number should be 1658 under column M, and 1657 under column N should
be returned into cell F37.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

How to code the formula?
 

*NON* array version:

=INDEX(N2:N27,MATCH(1,INDEX((M2:M27<=C37)*(M2:M27< ""),),))
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"RagDyeR" wrote in message
...
Your complication is that your datalist is in descending order.

Try this *array* formula:

=INDEX(N2:N27,MATCH(1,(M2:M27<=C37)*(M2:M27<""),0 ))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.


I'm assuming a match in Column M will also be valid.
If not, remove the = sign from
<=C37
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Eric" wrote in message
...
Does anyone have any suggestions on how to code the formula within Excel?
There is a list of values under M2:M27 in descending order with space in
between, and show below
M N
1871 1866


1849 1830
1830 1825

1786 1781

1658 1657
1641 1641
1630

There is a given number 1780 in cell C37, I would like to determine the
matched value under N column, and return it into F37, which find the max.
number under column M and this number is less than the given number 1780,
the
matched number should be 1658 under column M, and 1657 under column N should
be returned into cell F37.

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric