INDEX and MATCH
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
"T. Valko" wrote in message
...
Try this array formula** :
D2 = lookup value
=IF(D2MAX(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.
--
Biff
Microsoft Excel MVP
"PPL" wrote in message
...
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
|