Index / match combination

A

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

FARAZ QURESHI

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

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
 
A

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

FARAZ QURESHI

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

again an array formula.

Press Ctrl+Shift+Enter
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top