Complext function, match, address

[CHris]

Hi All

I'v e a list of numbers in A1:A10, i 've anumber in A15, i want to find the closest match in the list but not greater than A15, then return the cell 3 colums to the right
HOw could this be done

ty

 

[Frank Kabel]

Hi
if your data in A1:A10 is sorted ascending try
=INDEX(D110,MATCH(A15,A1:A10,1))

 

[Max]

Assuming the numbers in A1:A10 are in ascending order

Try in say, A17:

=OFFSET($A$1,MATCH(A15,$A$1:$A$10,1)-1,3)

--
Alternatively, with error trapping :

Put in A17:

=IF(ISNA(MATCH(A15,$A$1:$A$10,1)),"",OFFSET($A$1,MATCH(A15,$A$1:$A$10,1)-1,3
))

which will return blanks [""] for non matching cases, e.g. if A15
inadvertently contained text instead of numbers

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Hi Alll

I'v e a list of numbers in A1:A10, i 've anumber in A15, i want to find

the closest match in the list but not greater than A15, then return the cell
3 colums to the right.

 

[Peo Sjoblom]

One way, the values don't have to be sorted

=INDEX(C1:C10,MATCH(LARGE(A1:A10,COUNTIF(A1:A10,">"&A15)+1),A1:A10,0))

change the first range to D110 if you meant 3 columns from A starting with
B

--

Regards,

Peo Sjoblom

Hi Alll

I'v e a list of numbers in A1:A10, i 've anumber in A15, i want to find

the closest match in the list but not greater than A15, then return the cell
3 colums to the right.