Hello,

I am running Excel 2003. Im trying to accomplish a lookup.

On sheet 1, in column A, I have values such as: A, B, C, D. These values
repeat themselves. A small example:

a date place
b date place
b date place
a date place
c date place

On my second sheet, we'll call it the "a" sheet, I want this sheet to
basically list only the cells that had "a" in column A from sheet 1. So I
would want to see on this sheet (using above example)

a date place
a date place

I've tried using VLOOKUP, but am getting strange results. I think it's
because the values repeat themelves ..but Im not sure.

Any help is greatly appreciated.

Hi!

Try this:

Assume this table is on Sheet1 in the range A1:C5

a date place
b date place
b date place
a date place
c date place

Enter this formula as an array using the key combination of CTRL,SHIFT,ENTER
into SheetA cell A1:

=IF(ROWS($1:1)<=COUNTIF(Sheet1!$A$1:$A$5,"a"),INDE X(Sheet1!A$1:A$5,SMALL(IF(Sheet1!$A$1:$A$5="a",ROW (Sheet1!A$1:A$5)-ROW(Sheet1!A$1)+1),ROWS($1:1))),"")

Copy across to C1 then down until you get blanks.

Note: if you have 1000's of possible returns this may not be very efficient.

Biff

"Nick" wrote in message
...
Hello,

I am running Excel 2003. Im trying to accomplish a lookup.

On sheet 1, in column A, I have values such as: A, B, C, D. These values
repeat themselves. A small example:

a date place
b date place
b date place
a date place
c date place

On my second sheet, we'll call it the "a" sheet, I want this sheet to
basically list only the cells that had "a" in column A from sheet 1. So I
would want to see on this sheet (using above example)

a date place
a date place

I've tried using VLOOKUP, but am getting strange results. I think it's
because the values repeat themelves ..but Im not sure.

Any help is greatly appreciated.

P.S.

You'll probably have to format the date cells as DATE.

Biff

"Biff" wrote in message
...
Hi!

Try this:

Assume this table is on Sheet1 in the range A1:C5

a date place
b date place
b date place
a date place
c date place

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER into SheetA cell A1:

=IF(ROWS($1:1)<=COUNTIF(Sheet1!$A$1:$A$5,"a"),INDE X(Sheet1!A$1:A$5,SMALL(IF(Sheet1!$A$1:$A$5="a",ROW (Sheet1!A$1:A$5)-ROW(Sheet1!A$1)+1),ROWS($1:1))),"")

Copy across to C1 then down until you get blanks.

Note: if you have 1000's of possible returns this may not be very
efficient.

Biff

"Nick" wrote in message
...
Hello,

I am running Excel 2003. Im trying to accomplish a lookup.

On sheet 1, in column A, I have values such as: A, B, C, D. These values
repeat themselves. A small example:

a date place
b date place
b date place
a date place
c date place

On my second sheet, we'll call it the "a" sheet, I want this sheet to
basically list only the cells that had "a" in column A from sheet 1. So
I
would want to see on this sheet (using above example)

a date place
a date place

I've tried using VLOOKUP, but am getting strange results. I think it's
because the values repeat themelves ..but Im not sure.

Any help is greatly appreciated.

Thanks Biff..I'll give it a try!

"Biff" wrote:

P.S.

You'll probably have to format the date cells as DATE.

Biff

"Biff" wrote in message
...
Hi!

Try this:

Assume this table is on Sheet1 in the range A1:C5

a date place
b date place
b date place
a date place
c date place

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER into SheetA cell A1:

=IF(ROWS($1:1)<=COUNTIF(Sheet1!$A$1:$A$5,"a"),INDE X(Sheet1!A$1:A$5,SMALL(IF(Sheet1!$A$1:$A$5="a",ROW (Sheet1!A$1:A$5)-ROW(Sheet1!A$1)+1),ROWS($1:1))),"")

Copy across to C1 then down until you get blanks.

Note: if you have 1000's of possible returns this may not be very
efficient.

Biff

"Nick" wrote in message
...
Hello,

I am running Excel 2003. Im trying to accomplish a lookup.

On sheet 1, in column A, I have values such as: A, B, C, D. These values
repeat themselves. A small example:

a date place
b date place
b date place
a date place
c date place

On my second sheet, we'll call it the "a" sheet, I want this sheet to
basically list only the cells that had "a" in column A from sheet 1. So
I
would want to see on this sheet (using above example)

a date place
a date place

I've tried using VLOOKUP, but am getting strange results. I think it's
because the values repeat themelves ..but Im not sure.

Any help is greatly appreciated.