Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup between multiple columns
Is there a way to look up between multiple columns on the same worksheet. I
have 297,000 serial numbers that I need to search through. I exceed the allowed rows so had to but them in columns ex: Column A Column B Column C Comlumn D Column E Serial 0001 1/25/08 Serial 1023 5/01/08 Serial 0002 3/19/08 Serial 1034 6/23/08 Lookup Serial number Serial 0003 4/12/08 Serial 1056 7/14/08 Serial 0004 5/29/08 Serial 1078 8/28/08 On cell E2 I would like to search for the serial number and have it return the value on the cell next to it. ex: search Serial 1056 and return the 7/14/08 date. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup between multiple columns
Assuming every serial number is unique.
F1 = Serial 1056 =SUMIF(A:C,F1,B:D) Format as Date. Note how the range references are offset: A...B...C .......B...C...D -- Biff Microsoft Excel MVP "Mickey" wrote in message ... Is there a way to look up between multiple columns on the same worksheet. I have 297,000 serial numbers that I need to search through. I exceed the allowed rows so had to but them in columns ex: Column A Column B Column C Comlumn D Column E Serial 0001 1/25/08 Serial 1023 5/01/08 Serial 0002 3/19/08 Serial 1034 6/23/08 Lookup Serial number Serial 0003 4/12/08 Serial 1056 7/14/08 Serial 0004 5/29/08 Serial 1078 8/28/08 On cell E2 I would like to search for the serial number and have it return the value on the cell next to it. ex: search Serial 1056 and return the 7/14/08 date. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup between multiple columns
T.Valko,
Thanks, it worked perfect. When a serial number does not exist it returns a 0-Jan-00, is there a way to make it return "not valid" using the same formula? Thanks, "T. Valko" wrote: Assuming every serial number is unique. F1 = Serial 1056 =SUMIF(A:C,F1,B:D) Format as Date. Note how the range references are offset: A...B...C .......B...C...D -- Biff Microsoft Excel MVP "Mickey" wrote in message ... Is there a way to look up between multiple columns on the same worksheet. I have 297,000 serial numbers that I need to search through. I exceed the allowed rows so had to but them in columns ex: Column A Column B Column C Comlumn D Column E Serial 0001 1/25/08 Serial 1023 5/01/08 Serial 0002 3/19/08 Serial 1034 6/23/08 Lookup Serial number Serial 0003 4/12/08 Serial 1056 7/14/08 Serial 0004 5/29/08 Serial 1078 8/28/08 On cell E2 I would like to search for the serial number and have it return the value on the cell next to it. ex: search Serial 1056 and return the 7/14/08 date. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup between multiple columns
Try this:
=IF(SUMIF(A:C,F1,B:D),SUMIF(A:C,F1,B:D),"not valid") -- Biff Microsoft Excel MVP "Mickey" wrote in message ... T.Valko, Thanks, it worked perfect. When a serial number does not exist it returns a 0-Jan-00, is there a way to make it return "not valid" using the same formula? Thanks, "T. Valko" wrote: Assuming every serial number is unique. F1 = Serial 1056 =SUMIF(A:C,F1,B:D) Format as Date. Note how the range references are offset: A...B...C .......B...C...D -- Biff Microsoft Excel MVP "Mickey" wrote in message ... Is there a way to look up between multiple columns on the same worksheet. I have 297,000 serial numbers that I need to search through. I exceed the allowed rows so had to but them in columns ex: Column A Column B Column C Comlumn D Column E Serial 0001 1/25/08 Serial 1023 5/01/08 Serial 0002 3/19/08 Serial 1034 6/23/08 Lookup Serial number Serial 0003 4/12/08 Serial 1056 7/14/08 Serial 0004 5/29/08 Serial 1078 8/28/08 On cell E2 I would like to search for the serial number and have it return the value on the cell next to it. ex: search Serial 1056 and return the 7/14/08 date. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup in multiple columns | Excel Worksheet Functions | |||
Lookup across multiple columns within multiple sheets | Excel Discussion (Misc queries) | |||
lookup using multiple columns | Excel Worksheet Functions | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
lookup across multiple columns | Excel Worksheet Functions |