Index / match combination

[Andrew]

In A1 to A5 have a list of values of which the same value can appear multiple
times, these values are words rather than numbers. In B1 to B5 I have a list
of times. I need to be able to find a value in column A and return, for that
value, the lowest correpsonding time. The value I am looking up is in C1 and
result should be in D1. Example;

Column A Column B

ABC 19:00
DEF 05:00
ABC 04:30
ABC 11:00
JKL 14:00
ABC 15:20

If i was had ABC in C1, the result in D1 would be 04:30.

Any help greatly appreciated. Thanks.

 

[FARAZ QURESHI]

Try this:
=MIN(IF(A1:A6="abc",B1:B6))
It is an array formula so press Ctrl+Shift+Enter

 

[T. Valko]

Try this array formula** :

=MIN(IF(A1:A5=C1,B1:B5))

** 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.

Format as hh:mm

 

[Andrew]

Thanks that is great. Only thing is, sometimes there is no value in column B
which means this formulae is returning 12:00 AM. Is there a way that the
formulae can only return a value where there is no blank in column B. Example
- if I had ABC in C1, the result in D1 should be 11:00. Currently it is
showing 12:00 AM;


ABC 19:00
DEF 05:00
ABC
ABC 11:00
JKL 14:00
ABC 15:20

Any further advice greatly appreciated. Thanks.

 

[FARAZ QURESHI]

Try this:
=MIN(IF(A1:A6="abc",IF(B1:B6>0,B1:B6)))

again an array formula.

Press Ctrl+Shift+Enter

 

[Andrew]

Final question! The only thing that does not work now is when there is no
"ABC" in Column A to find. When this happens I get the answer of 12:00 AM
again. Is there a way to say that if no "ABC" exists in Colun A then put a ""
as the answer? in D1.

Thanks again, Andrew.

 

[T. Valko]

Try this, array entered:

=IF(SUMIF(A1:A6,C1,B1:B6),MIN(IF((A1:A6=C1)*(B1:B6>0),B1:B6)),"")

 

[Andrew]

Thank you, that is great.

Try this, array entered:

=IF(SUMIF(A1:A6,C1,B1:B6),MIN(IF((A1:A6=C1)*(B1:B6>0),B1:B6)),"")

 

[T. Valko]

You're welcome. Thanks for the feedback!