=INDEX(Sheet20!$C$3:$C$19,SMALL(IF(Sheet20!$A$3:$A$19=$K$26,ROWS(Sheet20!$A$3:$A$19-ROWS(Sheet20!$A$3)+1),""),1))

[Nazerith]

=INDEX(Sheet20!$C$3:$C$19,SMALL(IF(Sheet20!$A$3:$A$19=$K$26,ROWS(Sheet20!$A$3:$A$19-ROWS(Sheet20!$A$3)+1),""),1))

This is not working I have times of

8:00 am
4:00 pm
5:00 pm
5:00 pm

all associated to K26
all i get back if i change the k factor is 5:00 pm

 

[barry houdini]

Typically you would use ROW function rather than ROWS.....and you have a parenthesis in the wrong place, try this version

=INDEX(Sheet20!$C$3:$C$19,SMALL(IF(Sheet20!$A$3:$A$19=$K$26,ROW(Sheet20!$A$3:$A$19)-ROW(Sheet20!$A$3)+1),1))

confirmed with CTRL+SHIFT+ENTER

you need to change the red 1 to 2, 3 etc for all instances.....or use ROWS function in place of the 1, e.g. if first formula is in Z2 use this version copied down

=INDEX(Sheet20!$C$3:$C$19,SMALL(IF(Sheet20!$A$3:$A$19=$K$26,ROW(Sheet20!$A$3:$A$19)-ROW(Sheet20!$A$3)+1),ROWS(Z$2:Z2)))

ROWS function at the end automatically increments by 1 each row so that you don't have to change the numbers manually.....

 

[Nazerith]

Thank you so much you are the man this helped me out so much.

 

[T. Valko]

=INDEX(Sheet20!$C$3:$C$19,SMALL(IF(Sheet20!$A$3:$A$19=$K$26,ROWS(Sheet20!$A$3:$A$19-ROWS(Sheet20!$A$3)+1),""),1))

This is not working I have times of

8:00 am
4:00 pm
5:00 pm
5:00 pm

all associated to K26
all i get back if i change the k factor is 5:00 pm

For a "cleaner" formula to do this see:

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3