A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

lookup to bring back multiple values



 
 
Thread Tools Display Modes
  #1  
Old May 28th 09, 12:33 PM posted to microsoft.public.excel.worksheet.functions
zakfalls
external usenet poster
 
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
Ads
  #2  
Old May 28th 09, 01:39 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,504
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  
Old May 28th 09, 03:36 PM posted to microsoft.public.excel.worksheet.functions
zakfalls
external usenet poster
 
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  
Old May 28th 09, 05:36 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 07:12 PM.


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