Index / match combination
 

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.

Index / match combination
 

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

"Andrew" wrote:

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.

Index / match combination
 

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

Index / match combination
 

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.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

Index / match combination
 

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

again an array formula.

Press Ctrl+Shift+Enter

"Andrew" wrote:

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.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

Index / match combination
 

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.

"FARAZ QURESHI" wrote:

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

again an array formula.

Press Ctrl+Shift+Enter

"Andrew" wrote:

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.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

Index / match combination
 

Try this, array entered:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

"FARAZ QURESHI" wrote:

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

again an array formula.

Press Ctrl+Shift+Enter

"Andrew" wrote:

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.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

Index / match combination
 

Thank you, that is great.

"T. Valko" wrote:

Try this, array entered:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

"FARAZ QURESHI" wrote:

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

again an array formula.

Press Ctrl+Shift+Enter

"Andrew" wrote:

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.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

Index / match combination
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
Thank you, that is great.

"T. Valko" wrote:

Try this, array entered:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.

"FARAZ QURESHI" wrote:

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

again an array formula.

Press Ctrl+Shift+Enter

"Andrew" wrote:

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.



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
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.