OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

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

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
bobkortlandt
Newbie
Newbie


Joined: 05 Feb 2012
Posts: 2

PostPosted: Sun Feb 05, 2012 7:14 am    Post subject: lookup ALL cells with specific content, not just the first. Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Feb 05, 2012 2:04 pm    Post subject: Reply with quote

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).


Last edited by ken johnson on Thu Sep 27, 2012 12:51 am; edited 1 time in total
Back to top
View user's profile Send private message
bobkortlandt
Newbie
Newbie


Joined: 05 Feb 2012
Posts: 2

PostPosted: Sun Feb 12, 2012 7:47 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group