Lookup: Multiple Occurances

[Jim]

If I have a worksheet with serial number and date, how can I lookup the
entries from another sheet and display ALL the entrys.

1234 01/08/2005
1235 02/08/2005
1236 04/08/2005
1234 06/09/2005
1236 03/09/2005
1236 10/10/2005

For example on a second sheet if I look up the serial I need this sheet to
display ALL entries relating to that serial?
i.e
1234 01/08/2005
06/09/2005

or
1238 04/08/2005

or
1236 04/08/2005
03/09/2005
10/10/2005

etc

 

[Peo Sjoblom]

One possible way

=INDEX('another sheet'!$B$1:$B$100,SMALL(IF('another
sheet'!$A$1:$A$100=$B$1,ROW('another sheet'!$A$1:$A$100)),ROW(1:1)))

where B1 holds the serial number to lookup, A1:A100 the serial numbers and
B1:B100 the dates

enter with ctrl + shift & enter

copy down until you get an error


--
Regards,

Peo Sjoblom

(No private emails please)

 

[Alan Beban]

Another way, if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

=VLookups(1234,A1:B100,2) array entered into a column of cells
sufficient to accommodate the number of occurrences.

Alan Beban

 

[Ashish Mathur]

Hi,

Assuming your list is in range A1:B7, enter the serial number for whivh you
want the data returned in A10. Now enter the following array formula
(Ctrl+Shift+Enter) in B10 and copy downwards

IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

Regards,

Ashish Mathur

 

[Biff]

Hi!

A couple of pointers:

IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX
($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

There's no need to index both columns A and B since you're only interested
in and going to return data from a single column.

Also, you don't need to include the Index at all in the error trap. Any
error generated by the formula will come from this portion:

SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))

So, with those suggestions the formula would be:

IF(ISERROR(SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))),"",INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))))

Biff

 

[Aladin Akyurek]

You need to replace ROW(1:1) with something else for robustness. The
idiom is not efficient when the range to process is very large, a point
I felt to point out at some occasions as in http://tinyurl.com/b6zk7.

Hi!

A couple of pointers:




There's no need to index both columns A and B since you're only interested
in and going to return data from a single column.

Also, you don't need to include the Index at all in the error trap. Any
error generated by the formula will come from this portion:

SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))

So, with those suggestions the formula would be:

IF(ISERROR(SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))),"",INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))))

Biff

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.