REPOST: Which Function to Use
 

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.

REPOST: Which Function to Use
 

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.

REPOST: Which Function to Use
 

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.

REPOST: Which Function to Use
 

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.