Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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$1:D1)<=E$1,INDEX(B$2:B$20,SMALL(IF(A$2: A$20=D$1,ROW(B$2:B$20)),ROWS(D$2:D2))-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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bring Back DBF! The GIS Industry uses it! | Excel Discussion (Misc queries) | |||
Bring back .dbf in Excel | Excel Discussion (Misc queries) | |||
Bring back lowercase values | Excel Worksheet Functions | |||
Bring Back Qualified Sum from another WB | Excel Worksheet Functions | |||
Bring back the old help format! | Excel Worksheet Functions |