lookup ALL cells with specific content, not just the first.

[bobkortlandt]

Hiya,

How to solve this problem:

sheet:

row a | row b

AAA | 23
BBB | 14
CCC | 10
AAA | 12
DDD | 15

now i want a list of all cells containing AAA and the value from row b, eg

AAA | 23
AAA | 12

It seems to me that it would be easy, but google let me down...

Who can help me?

Thnx, Bob

[ken johnson]

You could use a helper column to find the rows with "AAA" then use the SMALL function with the INDEX function to gather the corresponding column B values.
See columns D and E in attached file (Multi Lookup.ods).

Or you could use a complicated array formula. See column G in attached file.
Array formulae must be entered using the Ctrl+Shift+Enter PC key combination and the Ctrl key must be held down as you drag the fill handle when filling adjacent cells with an array formula.

Or you could use the DataPilot. See columns I and J in attached file.
Data must have headings.
On the DataPilot dialogue the Name heading was dragged to the "Page" area and the Number heading to the "Row" area.

http://www.mediafire.com/view/?elfgokj7dn39qj3

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

[bobkortlandt]

Hi Ken,

kinda busy these days, therefore a late answer.

I'm working on the array formula to fit it in my "real" sheet, looks promising!

I'll let you know the outcome.

Thnx, Bob