Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 28th 09, 12:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 3
Default lookup to bring back multiple values

Hi,

I have a sheet with various names in column A with attributes in col B, the
names can occur more than once. a normal vlookup will only bring back the
first value in col B which i know. but i want to bring back all the different
result in column b for a name which occurs more than once.

I know this invloves Arrays, but the eg ive seen is set up in a way where
the name you are looking is on the first line, and amending it does not
really work for me.

Does anyone have any suggestion or a better example of how to do this?

hope ive made this clear!!!

Cheers

  #2   Report Post  
Old May 28th 09, 01:39 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default lookup to bring back multiple values

Hi,

With your lookup name in E1 try this array formula. Put the formula in a
cell and drag down. When it starts producing #NUM errors then you have found
every occurrence of your lookup value

=INDEX($B$1:$B$1000,LARGE(($A$1:$A$1000=$E$1)*ROW( $A$1:$A$1000),COUNTIF($A$1:$A$1000,$E$1)+1-ROW(A1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"zakfalls" wrote:

Hi,

I have a sheet with various names in column A with attributes in col B, the
names can occur more than once. a normal vlookup will only bring back the
first value in col B which i know. but i want to bring back all the different
result in column b for a name which occurs more than once.

I know this invloves Arrays, but the eg ive seen is set up in a way where
the name you are looking is on the first line, and amending it does not
really work for me.

Does anyone have any suggestion or a better example of how to do this?

hope ive made this clear!!!

Cheers

  #3   Report Post  
Old May 28th 09, 03:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 3
Default lookup to bring back multiple values

Almost gives me what i need...i can work with this,

Thanks


"Mike H" wrote:

Hi,

With your lookup name in E1 try this array formula. Put the formula in a
cell and drag down. When it starts producing #NUM errors then you have found
every occurrence of your lookup value

=INDEX($B$1:$B$1000,LARGE(($A$1:$A$1000=$E$1)*ROW( $A$1:$A$1000),COUNTIF($A$1:$A$1000,$E$1)+1-ROW(A1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"zakfalls" wrote:

Hi,

I have a sheet with various names in column A with attributes in col B, the
names can occur more than once. a normal vlookup will only bring back the
first value in col B which i know. but i want to bring back all the different
result in column b for a name which occurs more than once.

I know this invloves Arrays, but the eg ive seen is set up in a way where
the name you are looking is on the first line, and amending it does not
really work for me.

Does anyone have any suggestion or a better example of how to do this?

hope ive made this clear!!!

Cheers

  #4   Report Post  
Old May 28th 09, 05:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default lookup to bring back multiple values

Try this...

Data in the range A2:B20

D1 = lookup name
E1 = formula to count how many records meet the condition

=COUNTIF(A2:A20,D1)

Array entered** in D2 and copied down until you get blanks:

=IF(ROWS(D$11)<=E$1,INDEX(B$2:B$20,SMALL(IF(A$2: A$20=D$1,ROW(B$2:B$20)),ROWS(D$22))-ROW(B$2)+1),"")

If your data is sorted so that the names are grouped together it's much
simpler.

--
Biff
Microsoft Excel MVP


"zakfalls" wrote in message
...
Almost gives me what i need...i can work with this,

Thanks


"Mike H" wrote:

Hi,

With your lookup name in E1 try this array formula. Put the formula in a
cell and drag down. When it starts producing #NUM errors then you have
found
every occurrence of your lookup value

=INDEX($B$1:$B$1000,LARGE(($A$1:$A$1000=$E$1)*ROW( $A$1:$A$1000),COUNTIF($A$1:$A$1000,$E$1)+1-ROW(A1)))

This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit the
formula
'you must enter it again with CTRL+Shift+Enter.

Mike

"zakfalls" wrote:

Hi,

I have a sheet with various names in column A with attributes in col B,
the
names can occur more than once. a normal vlookup will only bring back
the
first value in col B which i know. but i want to bring back all the
different
result in column b for a name which occurs more than once.

I know this invloves Arrays, but the eg ive seen is set up in a way
where
the name you are looking is on the first line, and amending it does not
really work for me.

Does anyone have any suggestion or a better example of how to do this?

hope ive made this clear!!!

Cheers





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bring Back DBF! The GIS Industry uses it! Jobhater Excel Discussion (Misc queries) 6 June 9th 10 07:57 PM
Bring back .dbf in Excel Laura Excel Discussion (Misc queries) 11 July 13th 09 10:20 PM
Bring back lowercase values Pat Excel Worksheet Functions 3 October 3rd 06 06:16 PM
Bring Back Qualified Sum from another WB Excel Worksheet Functions 4 April 8th 05 01:01 AM
Bring back the old help format! John Przybylski Excel Worksheet Functions 2 April 5th 05 06:24 PM


All times are GMT +1. The time now is 09:57 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017