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.

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.

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.

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.