Complext function, match, address

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...
the closest match in the list but not greater than A15, then return the cell
3 colums to the right.
 
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 D1:D10 if you meant 3 columns from A starting with
B

--

Regards,

Peo Sjoblom

CHris said:
Hi Alll

I'v e a list of numbers in A1:A10, i 've anumber in A15, i want to find
Click to expand...
the closest match in the list but not greater than A15, then return the cell
3 colums to the right.
 
Back
Top