Can array formula return text?

[R. Todd Miller]

I'm guessing that this can be done with a LOOKUP but I don't know
how...

I have an array formula {=sum((A5:A100=A1)*(B5:B100="Some
text")*C5:C100)}
The values in column A are dates.
The values in columns B and C are strings.

I'm trying to return the string in column C when the date in column A
and the string in column B meet the criteria (Column A has the same
date as cell A1 and column B equals "Some text").

Thanks in advance for the help.

 

[Dan E]

Todd,

Set up as follows
A B C
1 Date String 1 String 2
2 July 3, 2003 Steve
3 July 4, 2003 Bob Jo Ann
4 July 3, 2003 Steve Jane
5 July 5, 2003 Bob Tammy
6 July 6, 2003 Jeff Ellen
7 July 7, 2003 Jim Alana
8 July 2, 2003 Joe Mary
9 July 9, 2003 Dan Lori
10 July 1, 2003 Dave Charlene

=IF(VLOOKUP(A2,A3:C10,2,FALSE) = B2,VLOOKUP(A2,A3:C10,3,FALSE),"No Match")

A2 holds the date to look for
B2 holds the text to look for

This produces Jane as a result

Dan E

 

[R. Todd Miller]

Thank you very much for the reply Dan. I probably didn't give enough
information in my original post. The dates in column A are not
unique. The first part of the formula appears to return the FIRST
occurance of the value in column A. In other words, the formula works
if B2 equals the text V1 (this refers to 'vessel one' not cell V1).
If B2 equals 'V2', it doesn't find 'CH' because the first part of the
formula calculates to 'V1' (i.e, VLOOKUP(A2,A3:C27,2,FALSE) = 'V1').

Cell C2 formula: =IF(VLOOKUP(A2,A3:C27,2,FALSE) =
B2,VLOOKUP(A2,A3:C27,3,FALSE),"No Match")

A B C
1
2 Apr 08 2003 V2 No Match
3 Date Facility Final String
4 Apr 06 2003 V1 NJ
5 Apr 06 2003 V2 CH
6 Apr 06 2003 V3
7 Apr 06 2003 V4
8 Apr 06 2003 V5
9 Apr 06 2003 Yard
10 Apr 07 2003 V1 NJ
11 Apr 07 2003 V2 CH
12 Apr 07 2003 V3
13 Apr 07 2003 V4
14 Apr 07 2003 V5
15 Apr 07 2003 Yard
16 Apr 08 2003 V1 NJ
17 Apr 08 2003 V2 CH
18 Apr 08 2003 V3
19 Apr 08 2003 V4
20 Apr 08 2003 V5
21 Apr 08 2003 Yard
22 Apr 09 2003 V1 NJ
23 Apr 09 2003 V2 CH
24 Apr 09 2003 V3
25 Apr 09 2003 V4
26 Apr 09 2003 V5
27 Apr 09 2003 Yard

 

[Dan E]

Todd,
This is not the best method but it will work
Insert a row between B & C
When your data starts ie (April 6, 2003) & (V1) in A4 & B4 put
= A4 & B4 in cell C4 and copy down for all your data, don't worry about this
column screwing up your formatting you can hide it.

For your search use
=IF( VLOOKUP((A1&B1),C427,1)= (A1&B1),VLOOKUP((A1&B1),C427,2),"No
Match")

Dan E