|
| 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. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 26th, 2004, 08:37 PM
|
|||
|
|||
vlookup to return multiple lines
hi,
i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? thank you chris 
|
|
#2
May 26th, 2004, 08:47 PM
|
|||
|
|||
|
vlookup to return multiple lines
Hi
you may try downloadind Alan Beban's array functions (http://home.pacbell.net/beban) They'll include a function called VLOOKUPS (for returning multiple lookup results) -- Regards Frank Kabel Frankfurt, Germany "Christopher" schrieb im Newsbeitrag ... hi, i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? thank you chris
|
|
#3
May 26th, 2004, 09:04 PM
|
|||
|
|||
|
vlookup to return multiple lines
See if this old post addresses your question:
http://tinyurl.com/2x8k5 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Christopher" wrote in message ... hi, i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? thank you chris
|
|
#4
May 26th, 2004, 09:25 PM
|
|||
|
|||
|
vlookup to return multiple lines
Hi Frank,
Thanks for your response. VLookups sounds exactly what i'm looking for however i can't get vlookups or vlookupleft recognized by excel. =VLOOKUP(A7,'May 28'!D7:E23,3)this works fine. =VLookups(A7,'May 28'!D7:E23,2)gives me a #name? error. =VLookupleft(A7,'May 28'!D7:E23,2)gives me the same error. is there something i'm missing in inputting these formulas? your help is much appreciated chris -----Original Message----- Hi you may try downloadind Alan Beban's array functions (http://home.pacbell.net/beban) They'll include a function called VLOOKUPS (for returning multiple lookup results) -- Regards Frank Kabel Frankfurt, Germany "Christopher" schrieb im Newsbeitrag news:12dec01c44358$da4c40f0 ... hi, i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? thank you chris .
|
|
#5
May 26th, 2004, 09:36 PM
|
|||
|
|||
|
vlookup to return multiple lines
Hi
have you inserted the macros in your workbook? See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag ... Hi Frank, Thanks for your response. VLookups sounds exactly what i'm looking for however i can't get vlookups or vlookupleft recognized by excel. =VLOOKUP(A7,'May 28'!D7:E23,3)this works fine. =VLookups(A7,'May 28'!D7:E23,2)gives me a #name? error. =VLookupleft(A7,'May 28'!D7:E23,2)gives me the same error. is there something i'm missing in inputting these formulas? your help is much appreciated chris -----Original Message----- Hi you may try downloadind Alan Beban's array functions (http://home.pacbell.net/beban) They'll include a function called VLOOKUPS (for returning multiple lookup results) -- Regards Frank Kabel Frankfurt, Germany "Christopher" schrieb im Newsbeitrag news:12dec01c44358$da4c40f0 ... hi, i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? thank you chris .
|
|
#6
May 26th, 2004, 10:21 PM
|
|||
|
|||
|
vlookup to return multiple lines
this formula looks great but i'm having a little problem
interpreting it =INDEX(B2:B100,SMALL(IF(A2:A100=C1,ROW(A2:A100)-1,""),ROW (A2:A100)-1)) esentially the array i'd be looking at is from column c to column e of another sheet. i was trying to get column e of the array sheet(for the same row of the found data) in column b of the current sheet, and column d of the array sheet(again for the same row of the found data) in column c of the current sheet. i'm not exactly sure where in that formula i'd be defining which row to return a value. thanks for your help on this chris -----Original Message----- See if this old post addresses your question: http://tinyurl.com/2x8k5 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Christopher" wrote in message ... hi, i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? thank you chris .
|
|
#7
May 27th, 2004, 05:11 AM
|
|||
|
|||
|
vlookup to return multiple lines
I don't really understand what you're describing.
If you could you be more specific, say with cell addresses, I would be glad to make some suggestions. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... this formula looks great but i'm having a little problem interpreting it =INDEX(B2:B100,SMALL(IF(A2:A100=C1,ROW(A2:A100)-1,""),ROW (A2:A100)-1)) esentially the array i'd be looking at is from column c to column e of another sheet. i was trying to get column e of the array sheet(for the same row of the found data) in column b of the current sheet, and column d of the array sheet(again for the same row of the found data) in column c of the current sheet. i'm not exactly sure where in that formula i'd be defining which row to return a value. thanks for your help on this chris -----Original Message----- See if this old post addresses your question: http://tinyurl.com/2x8k5 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Christopher" wrote in message ... hi, i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? thank you chris .
|
|
#8
May 27th, 2004, 06:22 PM
|
|||
|
|||
|
vlookup to return multiple lines
"Christopher" wrote...
i'm using a vlookup to find data which is in an array mutliple times. The problem is that the vlookup only returns the first occurance of the data i'm looking for. Does anybody know of an alternative formula that will give multiple returns? If you could live with finding them piece by piece rather than as a single array, you could use the following array formulas. H2 (topmost entry, not an array formula): =VLOOKUP($D$1,$B$2:$C$50,2,0) H3 (second entry, ARRAY FORMULA): =INDEX($C$2:$C$50,MATCH(1,($B$2:$B$50=$D$1)-COUNTIF(H$2:H2,$C$2:$C$50),0)) Select H3 and fill down as far as needed. It'll evaluate to #N/A error values when the matching entries in the table have been exhausted. -- To top-post is human, to bottom-post and snip is sublime.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
