Multiple Matches

[carl]

I have a data table like this (Table1)

Sequence Date Time ExternalSymbol FirmId
1 20060929 9:30:18 RUPL06C95.00 BOX549
2 20060929 9:30:18 CYQA07C27.50 BOX017
3 20060929 9:30:22 NQ A07C12.50 BOX917
3 20060929 9:30:22 NQ A07C12.50 BOX017
5 20060929 9:30:22 RUPV06P90.00 BOX549
5 20060929 9:30:22 RUPV06P90.00 BOX017
7 20060929 9:30:24 RUPM07P70.00 BOX017

I am looking for a formula for COL A of Table 2 below that will look at
Data, Time, and External Symbol, compare to Table 1 above, and if all 3 match
for a given row, return the sequence number from Table 1 or return "Not Found"

Table2
Sequence# Date TimeConv FirmId ExternalSymbol
1 20060929 9:30:18 BOX017 RUPL06C95.00
Not Found 20060929 11:08:41 BOX017 HD N07P32.50
Not Found 20060929 11:16:58 BOX017 QAAD07C85.00
Not Found 20060929 11:35:39 BOX017 CCQD07C37.50
Not Found 20060929 11:44:15 BOX017 AAOD07C40.00
Not Found 20060929 12:26:32 BOX017 KDUV06P160.00
Not Found 20060929 12:26:35 BOX017 MMMP07P65.00

Thank you in advance.

 

[Stefi]

=IF(OR(ISERROR(VLOOKUP(B2,Table1!B:B,1,FALSE)),ISERROR(VLOOKUP(C2,Table1!C:C,1,FALSE)),ISERROR(VLOOKUP(E2,Table1!D,1,FALSE))),"Not
Found",INDEX(Table1!A:A,MATCH(B2,Table1!B:B,0)))


Regards,
Stefi

„carl†ezt írta:

 

[Bob Phillips]

=IF(ISERROR(MATCH(1,(Sheet1!$B$1:$B$100=Sheet2!B2)*(Sheet1!$C$1:$C$100=Sheet
2!C2)*(Sheet1!$D$1:$D$100=Sheet2!D2),0)),"",
INDEX(Sheet1!A1:A100,MATCH(1,(Sheet1!$B$1:$B$100=Sheet2!B2)*(Sheet1!$C$1:$C$
100=Sheet2!C2)*(Sheet1!$D$1:$D$100=Sheet2!D2),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Teethless mama]

=IF(ISNA(MATCH(1,(Date=B13)*(Time=C13)*(ExternalSymbol=E13),0)),"Not
Found",INDEX(Sequence,MATCH(1,(Date=B13)*(Time=C13)*(ExternalSymbol=E13),0)))

ctrl > shift > enter