Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Matches
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Matches
=IF(OR(ISERROR(VLOOKUP(B2,Table1!B:B,1,FALSE)),ISE RROR(VLOOKUP(C2,Table1!C:C,1,FALSE)),ISERROR(VLOOK UP(E2,Table1!D:D,1,FALSE))),"Not
Found",INDEX(Table1!A:A,MATCH(B2,Table1!B:B,0))) Regards, Stefi €˛carl€¯ ezt Ć*rta: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Matches
=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=S heet2!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) "carl" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Matches
=IF(ISNA(MATCH(1,(Date=B13)*(Time=C13)*(ExternalSy mbol=E13),0)),"Not
Found",INDEX(Sequence,MATCH(1,(Date=B13)*(Time=C13 )*(ExternalSymbol=E13),0))) ctrl shift enter "carl" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
work with multiple workbooks on separate monitor for 2003 edition | Excel Worksheet Functions | |||
How do I retrieve & view multiple matches from another workbook? | Excel Worksheet Functions | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Multiple matches on VLOOKUP | Excel Worksheet Functions |