INDEX and MATCH

[PPL]

Hi,
Excel 2003:
I have a column of figures in ascending order (can't be reveresed)

I'm trying to use INDEX and MATCH to find a value that is MORE THAN or equal
to a lookup_value. (and then to pick an adjacent cell value)


MATCH works great to recover the LESS THAN or equal value.

Is there another formula that I should be using?
TIA

Phil

 

[T. Valko]

Try this array formula** :

D2 = lookup value

=IF(D2>MAX(A1:A10),"out of range",INDEX(B1:B10,MATCH(TRUE,A1:A10>=D2,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[PPL]

Hi Biff,

Thanks for getting back to me. Sorry it's taken me so long to reply.

Undoubtedly I should have given more info. However, I found a solution that
met my needs.

I used a combination of OFFSET, INDEX and MATCH

The problem I had was that when using INDEX and MATCH alone the result
always returned the LESS THAN or equal value.

I used OFFSET to pick the result from the cell in the same column but next
row as follows:

Sheet2 contains names of people on-call & dates

Column C = List of names

Column D = List of dates

Using index & match alone allows me to easily see who is currently on call
(current date is entered in C2 in the current sheet) (i.e. Sheet1) but it
doesn't allow me to see who is the next person in the list and when they
start their duty. The following works for me.



=OFFSET(INDEX(Sheet2!$C$6:$C$100,MATCH($C$2,Sheet2!$D$6:$D$100),1),1,0)

Thanks again.

I hope this helps someone else

Phil

Try this array formula** :

D2 = lookup value

=IF(D2>MAX(A1:A10),"out of range",INDEX(B1:B10,MATCH(TRUE,A1:A10>=D2,0)))

** array formulas need to be entered using the key combination of

CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT

 

[T. Valko]

Good deal. Thanks for the feedback!