How to code the formula?

[Eric]

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

 

[Derrick]

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]

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]

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.

 

[RagDyeR]

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!
=====================================================

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

 

[RagDyeR]

*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 !
-----------------------------------------------------------------------------------------------


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!
=====================================================

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