| Home |
| Search |
| Today's Posts |
|
#41
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to return mulitple corresponding values
Yes, I'm definitely interested. I know in reality that it can't be unlimited,
but I need at least 10 names appended. I'll also have to figure out how to move anything over 30 characters to the next line, but first things first. Can this function handle multiple reoccurring group names (i.e. loop within a loop)? group name marketing mjagger marketing rthomas marketing xbono accounting rcharles accounting jbrown hr jmayer it jjohnson it bdylan it jjoplin it akiedis it braitt output: mjagger^rthomas^xbono rcharles^jbrown jmayer jjohnson^bdylan^jjoplin^akiedis^braitt The looping seems to be the limitation I'm running into with the index function or I'm using it incorrectly. "T. Valko" wrote: There can be...unlimited names for a group. Well, that's not good! You have to narrow down "unlimited". There is a free add-in available that has a function that will do this but the resulting string is *limited* to no more than 255 characters. So, that means "tvalko^debih^biff" can't be more than 255 chars. Are you interested in this? -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I have reoccuring group names in column A and multiple names (i.e tvalko, debih, biff, etc.) in column B for each group. There can be one name or unlimited names for a group. I want to identify the name s for each different group and obtain the list on one line (tvalko^debih^biff) with carats as delimeters. "T. Valko" wrote: You need to be more specific and provide some details. -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I'm also trying to do this. I need to lookup a value in one column that returns multiple values in the second column, but I want to list it out with "^" between the values. I need to do this dynamically for multiple lookup values. Can you help me? "Biff" wrote: You can do that, however, since the formula returns possible multiple results for each lookup value you'd have to use another formula (the same one, just change =$A$60 to the next cell reference). Biff "Debi H" wrote in message ... I would like to copy down and do this for all the values in the list not just the value from A60 "Biff" wrote: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) Use this: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? What do you mean? Biff "Debi H" wrote in message ... One more question please.... If the fromula: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? "Biff" wrote: You're welcome! Biff "Debi H" wrote in message ... That worked...thanks "Biff" wrote: Type the formula then, instead of hitting ENTER like you normally would, hold down both the CTRL key AND the SHIFT key then hit ENTER. When done properly Excel will enclose the formula in squihhly braces { }. You can't just type these braces in, you MUST use the key combination to produce them. If you're still having problems I'll be glad to look at your file and see if I can figure it out. Just let me know how to contact you. Biff "Debi H" wrote in message ... That still does not work for me. Am I missing something? I did the key stroke of (CTRL+SHIFT+ENTER) 1ST Entered the formula and I get the VALUE error "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#42
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Download and install the free Morefunc.xll add-in from:
http://xcell05.free.fr/morefunc/english/index.htm Then, based on the sample data you posted... Assuming this data is in the range A2:B12. You have the unique groups listed in the range E2:E5. Enter this array formula** in F2 and copy down to F5: =SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12," ")&" "))," ","^") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Melanie" wrote in message ... Yes, I'm definitely interested. I know in reality that it can't be unlimited, but I need at least 10 names appended. I'll also have to figure out how to move anything over 30 characters to the next line, but first things first. Can this function handle multiple reoccurring group names (i.e. loop within a loop)? group name marketing mjagger marketing rthomas marketing xbono accounting rcharles accounting jbrown hr jmayer it jjohnson it bdylan it jjoplin it akiedis it braitt output: mjagger^rthomas^xbono rcharles^jbrown jmayer jjohnson^bdylan^jjoplin^akiedis^braitt The looping seems to be the limitation I'm running into with the index function or I'm using it incorrectly. "T. Valko" wrote: There can be...unlimited names for a group. Well, that's not good! You have to narrow down "unlimited". There is a free add-in available that has a function that will do this but the resulting string is *limited* to no more than 255 characters. So, that means "tvalko^debih^biff" can't be more than 255 chars. Are you interested in this? -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I have reoccuring group names in column A and multiple names (i.e tvalko, debih, biff, etc.) in column B for each group. There can be one name or unlimited names for a group. I want to identify the name s for each different group and obtain the list on one line (tvalko^debih^biff) with carats as delimeters. "T. Valko" wrote: You need to be more specific and provide some details. -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I'm also trying to do this. I need to lookup a value in one column that returns multiple values in the second column, but I want to list it out with "^" between the values. I need to do this dynamically for multiple lookup values. Can you help me? "Biff" wrote: You can do that, however, since the formula returns possible multiple results for each lookup value you'd have to use another formula (the same one, just change =$A$60 to the next cell reference). Biff "Debi H" wrote in message ... I would like to copy down and do this for all the values in the list not just the value from A60 "Biff" wrote: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) Use this: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? What do you mean? Biff "Debi H" wrote in message ... One more question please.... If the fromula: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? "Biff" wrote: You're welcome! Biff "Debi H" wrote in message ... That worked...thanks "Biff" wrote: Type the formula then, instead of hitting ENTER like you normally would, hold down both the CTRL key AND the SHIFT key then hit ENTER. When done properly Excel will enclose the formula in squihhly braces { }. You can't just type these braces in, you MUST use the key combination to produce them. If you're still having problems I'll be glad to look at your file and see if I can figure it out. Just let me know how to contact you. Biff "Debi H" wrote in message ... That still does not work for me. Am I missing something? I did the key stroke of (CTRL+SHIFT+ENTER) 1ST Entered the formula and I get the VALUE error "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#43
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Thanks! That worked! Although, for some reason, it wouldn't let me reference
the unique list (col E) from a pivot table in another worksheet so I referenced the unique list using = in the same worksheet and for some reason that worked(?). I'm just happy it works! I've been working on this on-and-off for a week. "T. Valko" wrote: Download and install the free Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm Then, based on the sample data you posted... Assuming this data is in the range A2:B12. You have the unique groups listed in the range E2:E5. Enter this array formula** in F2 and copy down to F5: =SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12," ")&" "))," ","^") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Melanie" wrote in message ... Yes, I'm definitely interested. I know in reality that it can't be unlimited, but I need at least 10 names appended. I'll also have to figure out how to move anything over 30 characters to the next line, but first things first. Can this function handle multiple reoccurring group names (i.e. loop within a loop)? group name marketing mjagger marketing rthomas marketing xbono accounting rcharles accounting jbrown hr jmayer it jjohnson it bdylan it jjoplin it akiedis it braitt output: mjagger^rthomas^xbono rcharles^jbrown jmayer jjohnson^bdylan^jjoplin^akiedis^braitt The looping seems to be the limitation I'm running into with the index function or I'm using it incorrectly. "T. Valko" wrote: There can be...unlimited names for a group. Well, that's not good! You have to narrow down "unlimited". There is a free add-in available that has a function that will do this but the resulting string is *limited* to no more than 255 characters. So, that means "tvalko^debih^biff" can't be more than 255 chars. Are you interested in this? -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I have reoccuring group names in column A and multiple names (i.e tvalko, debih, biff, etc.) in column B for each group. There can be one name or unlimited names for a group. I want to identify the name s for each different group and obtain the list on one line (tvalko^debih^biff) with carats as delimeters. "T. Valko" wrote: You need to be more specific and provide some details. -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I'm also trying to do this. I need to lookup a value in one column that returns multiple values in the second column, but I want to list it out with "^" between the values. I need to do this dynamically for multiple lookup values. Can you help me? "Biff" wrote: You can do that, however, since the formula returns possible multiple results for each lookup value you'd have to use another formula (the same one, just change =$A$60 to the next cell reference). Biff "Debi H" wrote in message ... I would like to copy down and do this for all the values in the list not just the value from A60 "Biff" wrote: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) Use this: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? What do you mean? Biff "Debi H" wrote in message ... One more question please.... If the fromula: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? "Biff" wrote: You're welcome! Biff "Debi H" wrote in message ... That worked...thanks "Biff" wrote: Type the formula then, instead of hitting ENTER like you normally would, hold down both the CTRL key AND the SHIFT key then hit ENTER. When done properly Excel will enclose the formula in squihhly braces { }. You can't just type these braces in, you MUST use the key combination to produce them. If you're still having problems I'll be glad to look at your file and see if I can figure it out. Just let me know how to contact you. Biff "Debi H" wrote in message ... That still does not work for me. Am I missing something? I did the key stroke of (CTRL+SHIFT+ENTER) 1ST Entered the formula and I get the VALUE error "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#44
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Melanie" wrote in message ... Thanks! That worked! Although, for some reason, it wouldn't let me reference the unique list (col E) from a pivot table in another worksheet so I referenced the unique list using = in the same worksheet and for some reason that worked(?). I'm just happy it works! I've been working on this on-and-off for a week. "T. Valko" wrote: Download and install the free Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm Then, based on the sample data you posted... Assuming this data is in the range A2:B12. You have the unique groups listed in the range E2:E5. Enter this array formula** in F2 and copy down to F5: =SUBSTITUTE(TRIM(MCONCAT(IF(A$2:A$12=E2,B$2:B$12," ")&" "))," ","^") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Melanie" wrote in message ... Yes, I'm definitely interested. I know in reality that it can't be unlimited, but I need at least 10 names appended. I'll also have to figure out how to move anything over 30 characters to the next line, but first things first. Can this function handle multiple reoccurring group names (i.e. loop within a loop)? group name marketing mjagger marketing rthomas marketing xbono accounting rcharles accounting jbrown hr jmayer it jjohnson it bdylan it jjoplin it akiedis it braitt output: mjagger^rthomas^xbono rcharles^jbrown jmayer jjohnson^bdylan^jjoplin^akiedis^braitt The looping seems to be the limitation I'm running into with the index function or I'm using it incorrectly. "T. Valko" wrote: There can be...unlimited names for a group. Well, that's not good! You have to narrow down "unlimited". There is a free add-in available that has a function that will do this but the resulting string is *limited* to no more than 255 characters. So, that means "tvalko^debih^biff" can't be more than 255 chars. Are you interested in this? -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I have reoccuring group names in column A and multiple names (i.e tvalko, debih, biff, etc.) in column B for each group. There can be one name or unlimited names for a group. I want to identify the name s for each different group and obtain the list on one line (tvalko^debih^biff) with carats as delimeters. "T. Valko" wrote: You need to be more specific and provide some details. -- Biff Microsoft Excel MVP "Melanie" wrote in message ... I'm also trying to do this. I need to lookup a value in one column that returns multiple values in the second column, but I want to list it out with "^" between the values. I need to do this dynamically for multiple lookup values. Can you help me? "Biff" wrote: You can do that, however, since the formula returns possible multiple results for each lookup value you'd have to use another formula (the same one, just change =$A$60 to the next cell reference). Biff "Debi H" wrote in message ... I would like to copy down and do this for all the values in the list not just the value from A60 "Biff" wrote: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) Use this: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)-ROW(A$2)+1),ROWS($1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? What do you mean? Biff "Debi H" wrote in message ... One more question please.... If the fromula: =INDEX($A2:$H15,SMALL(IF($A$2:$A$15=$A$60,ROW($A$2 :$A$15)),ROW(1:1)),5) can I change $A$2:$A$15=$A$60 to look up a range? "Biff" wrote: You're welcome! Biff "Debi H" wrote in message ... That worked...thanks "Biff" wrote: Type the formula then, instead of hitting ENTER like you normally would, hold down both the CTRL key AND the SHIFT key then hit ENTER. When done properly Excel will enclose the formula in squihhly braces { }. You can't just type these braces in, you MUST use the key combination to produce them. If you're still having problems I'll be glad to look at your file and see if I can figure it out. Just let me know how to contact you. Biff "Debi H" wrote in message ... That still does not work for me. Am I missing something? I did the key stroke of (CTRL+SHIFT+ENTER) 1ST Entered the formula and I get the VALUE error "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#45
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Hi Biff
Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#46
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Refresh my memory.
-- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#47
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Sorry .. didnt get wad you mean...
-- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#48
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Explain what you're wanting to do.
I know what the formula does but what do you mean by: sorted order i.e.the most recent activity showing up first? That sounds to me like you're looking for dates? -- Biff Microsoft Excel MVP "nikko" wrote in message ... Sorry .. didnt get wad you mean... -- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#49
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
the returned values are dates format... & i jus want to return the dates from
oldest to newest... -- nikko "T. Valko" wrote: Explain what you're wanting to do. I know what the formula does but what do you mean by: sorted order i.e.the most recent activity showing up first? That sounds to me like you're looking for dates? -- Biff Microsoft Excel MVP "nikko" wrote in message ... Sorry .. didnt get wad you mean... -- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? |
|
#50
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
This will extract the dates in ascending order, oldest to newest, that meet
the condition. I'm leaving out the sheet name so be sure to add it to your formula. Let's assume you want the results starting in cell X1. Array entered** : =SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)) Copy down until you get #NUM! errors meaning all applicable data has been extracted. If you want an error trap: =IF(ROWS(X$1:X1)<=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"") Copy down until you get blanks meaning all applicable data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "nikko" wrote in message ... the returned values are dates format... & i jus want to return the dates from oldest to newest... -- nikko "T. Valko" wrote: Explain what you're wanting to do. I know what the formula does but what do you mean by: sorted order i.e.the most recent activity showing up first? That sounds to me like you're looking for dates? -- Biff Microsoft Excel MVP "nikko" wrote in message ... Sorry .. didnt get wad you mean... -- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? |
|
#51
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Thanks ... it works perfectly.
Now with the dates returned in ascending order, i'm trying to return the related activity comments and contact person. however if there are 2 similar dates with different contact person, the formula does not seen to work .. desired results Date Contact Person 25-Mar Peter 25-Mar Alan formula is returning Date Contact Person 25-Mar Peter 25-Mar Peter Formula - (INDEX('Data3-Opp Activities Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp Activities Info'!$C$1:$C$10000)),ROW(1:1)),7))) -- nikko "T. Valko" wrote: This will extract the dates in ascending order, oldest to newest, that meet the condition. I'm leaving out the sheet name so be sure to add it to your formula. Let's assume you want the results starting in cell X1. Array entered** : =SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)) Copy down until you get #NUM! errors meaning all applicable data has been extracted. If you want an error trap: =IF(ROWS(X$1:X1)<=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"") Copy down until you get blanks meaning all applicable data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "nikko" wrote in message ... the returned values are dates format... & i jus want to return the dates from oldest to newest... -- nikko "T. Valko" wrote: Explain what you're wanting to do. I know what the formula does but what do you mean by: sorted order i.e.the most recent activity showing up first? That sounds to me like you're looking for dates? -- Biff Microsoft Excel MVP "nikko" wrote in message ... Sorry .. didnt get wad you mean... -- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and |
|
#52
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
if there are 2 similar dates with different contact person
Ok, it gets complicated! Here's a small sample file that demonstrates this. xNikko.xls 17kb http://cjoint.com/?efhZw8gEFM -- Biff Microsoft Excel MVP "nikko" wrote in message ... Thanks ... it works perfectly. Now with the dates returned in ascending order, i'm trying to return the related activity comments and contact person. however if there are 2 similar dates with different contact person, the formula does not seen to work .. desired results Date Contact Person 25-Mar Peter 25-Mar Alan formula is returning Date Contact Person 25-Mar Peter 25-Mar Peter Formula - (INDEX('Data3-Opp Activities Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp Activities Info'!$C$1:$C$10000)),ROW(1:1)),7))) -- nikko "T. Valko" wrote: This will extract the dates in ascending order, oldest to newest, that meet the condition. I'm leaving out the sheet name so be sure to add it to your formula. Let's assume you want the results starting in cell X1. Array entered** : =SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)) Copy down until you get #NUM! errors meaning all applicable data has been extracted. If you want an error trap: =IF(ROWS(X$1:X1)<=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"") Copy down until you get blanks meaning all applicable data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "nikko" wrote in message ... the returned values are dates format... & i jus want to return the dates from oldest to newest... -- nikko "T. Valko" wrote: Explain what you're wanting to do. I know what the formula does but what do you mean by: sorted order i.e.the most recent activity showing up first? That sounds to me like you're looking for dates? -- Biff Microsoft Excel MVP "nikko" wrote in message ... Sorry .. didnt get wad you mean... -- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and |
|
#53
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Thanks ... works perfectly!!! :)
-- nikko "T. Valko" wrote: if there are 2 similar dates with different contact person Ok, it gets complicated! Here's a small sample file that demonstrates this. xNikko.xls 17kb http://cjoint.com/?efhZw8gEFM -- Biff Microsoft Excel MVP "nikko" wrote in message ... Thanks ... it works perfectly. Now with the dates returned in ascending order, i'm trying to return the related activity comments and contact person. however if there are 2 similar dates with different contact person, the formula does not seen to work .. desired results Date Contact Person 25-Mar Peter 25-Mar Alan formula is returning Date Contact Person 25-Mar Peter 25-Mar Peter Formula - (INDEX('Data3-Opp Activities Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp Activities Info'!$C$1:$C$10000)),ROW(1:1)),7))) -- nikko "T. Valko" wrote: This will extract the dates in ascending order, oldest to newest, that meet the condition. I'm leaving out the sheet name so be sure to add it to your formula. Let's assume you want the results starting in cell X1. Array entered** : =SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)) Copy down until you get #NUM! errors meaning all applicable data has been extracted. If you want an error trap: =IF(ROWS(X$1:X1)<=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"") Copy down until you get blanks meaning all applicable data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "nikko" wrote in message ... the returned values are dates format... & i jus want to return the dates from oldest to newest... -- nikko "T. Valko" wrote: Explain what you're wanting to do. I know what the formula does but what do you mean by: sorted order i.e.the most recent activity showing up first? That sounds to me like you're looking for dates? -- Biff Microsoft Excel MVP "nikko" wrote in message ... Sorry .. didnt get wad you mean... -- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: |
|
#54
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "nikko" wrote in message ... Thanks ... works perfectly!!! :) -- nikko "T. Valko" wrote: if there are 2 similar dates with different contact person Ok, it gets complicated! Here's a small sample file that demonstrates this. xNikko.xls 17kb http://cjoint.com/?efhZw8gEFM -- Biff Microsoft Excel MVP "nikko" wrote in message ... Thanks ... it works perfectly. Now with the dates returned in ascending order, i'm trying to return the related activity comments and contact person. however if there are 2 similar dates with different contact person, the formula does not seen to work .. desired results Date Contact Person 25-Mar Peter 25-Mar Alan formula is returning Date Contact Person 25-Mar Peter 25-Mar Peter Formula - (INDEX('Data3-Opp Activities Info'!$A$1:$J$10000,SMALL(IF('Data3-Opp Activities Info'!$C$1:$C$10000='Opportunity (3)'!$J$2&H25,ROW('Data3-Opp Activities Info'!$C$1:$C$10000)),ROW(1:1)),7))) -- nikko "T. Valko" wrote: This will extract the dates in ascending order, oldest to newest, that meet the condition. I'm leaving out the sheet name so be sure to add it to your formula. Let's assume you want the results starting in cell X1. Array entered** : =SMALL(IF(A$1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)) Copy down until you get #NUM! errors meaning all applicable data has been extracted. If you want an error trap: =IF(ROWS(X$1:X1)<=COUNTIF(A$1:A$13,J$2),SMALL(IF(A $1:A$13=J$2,H$1:H$13),ROWS(X$1:X1)),"") Copy down until you get blanks meaning all applicable data has been extracted. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "nikko" wrote in message ... the returned values are dates format... & i jus want to return the dates from oldest to newest... -- nikko "T. Valko" wrote: Explain what you're wanting to do. I know what the formula does but what do you mean by: sorted order i.e.the most recent activity showing up first? That sounds to me like you're looking for dates? -- Biff Microsoft Excel MVP "nikko" wrote in message ... Sorry .. didnt get wad you mean... -- nikko "T. Valko" wrote: Refresh my memory. -- Biff Microsoft Excel MVP "nikko" wrote in message ... Hi Biff Using below formula and its returning the values not in sequence =INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1))) is there a way to return the corresponding values in sorted order i.e. w the most recent activity showing up first? Thanks you! -- nikko "Biff" wrote: in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. No, it's not a problem. It's not actually multiplying numbers. It's multiplying logical tests that return either TRUE or FALSE. For example: ('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000) This will return an array like this: (TRUE)*(TRUE) (TRUE)*(FALSE) (FALSE)*(TRUE) (FALSE)*(FALSE) These logicals are multiplied together and result in an array of 1's or 0's where 1 evalautes to the value_if_true and 0 evaluates to the value_if_false. The value_if_true is a number from 1 to 500 that's derived from this expression: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) When the value_if_true condition is met then the corresponding numbers from that expression are then passed to the Small function. ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) If you're not using dynamic named ranges then I would "dummy down" this portion: ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)) To: ROW($1:$500) You don't actually need the sheet name or the cell references but you'll see where some people use them just for a better understanding. Biff "gfactor" wrote in message ... biff, thanks for your help...getting an error. here is what you recommended: =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) ..here is my exact formula (Array entered) =INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500)))),ROW('PO Detail'!B18))) its still bringing back values from range 2 if range 3 is 0. in the formula, it looks like were mutliplying the rng2 valule by the corresponding value in rng3, however the rng2 value is not a number. it is in most cases text. not sure if that is the problem. maybe we can just check to see if rng3 is 0? i tried this, but syntax wasn't right. any thoughts? thanks in advance, g "Biff" wrote: i only want the result delivered if the value in (rng3) is "0" Try this: (array entered) =INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... biff - you nailed it kid. got the result i was looking for in terms of being able to have a variable length array. here's my next question: lets assume my formula reads as follows: =INDEX(Rng2,SMALL(IF(Rng1=$z$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) ...and my data looks like this: a(rng2) b(rng1) c (rng4) 1 y1 p1 5 2 x1 p2 1 3 y1 p3 0 4 z1 p4 3 5 q1 p5 4 if my $z$1 value is "y1"...the formula returns 2 data sets: y1 p1 y1 p3 the thing is, i only want the result delivered if the value in (rng3) is "0" can you help me? thanks in advance. g "Biff" wrote: |
|
#55
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Hi Biff,
I've read quite a few of the posts on returning multiple corresponding values but everything is basically over my head. I don't have any training or exp in this stuff. I've tried to replicate the example at: http://office.microsoft.com/en-us/ex...0551033#Remove I was able to return only one value for ashish 234. I couldn't get the formula to return 534 or 834. Here's the formula I have. =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2) Even if I get this right, I'm not sure if it is what I need. I have a data set where one value can have multiple corresponding values. It's not a 1:1 relationship. I want all values returned but only the distinct values. Here's an example of the data. Acct Line 1010 12 1010 12 1010 12 1010 12 1010 12 1010 5 1010 7 1010 5 1020 22 1020 13 1030 10 This is what I want the result to look like. 1010 12 5 7 1020 22 13 1030 10 Can you help, please? "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#56
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
This is what I want the result to look like.
1010 12 5 7 1020 22 13 1030 10 I can get the result to look like this: 1010...5...7...12 1020...13...22 1030...10 Do you want to go with that? -- Biff Microsoft Excel MVP "jbf" wrote in message ... Hi Biff, I've read quite a few of the posts on returning multiple corresponding values but everything is basically over my head. I don't have any training or exp in this stuff. I've tried to replicate the example at: http://office.microsoft.com/en-us/ex...0551033#Remove I was able to return only one value for ashish 234. I couldn't get the formula to return 534 or 834. Here's the formula I have. =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2) Even if I get this right, I'm not sure if it is what I need. I have a data set where one value can have multiple corresponding values. It's not a 1:1 relationship. I want all values returned but only the distinct values. Here's an example of the data. Acct Line 1010 12 1010 12 1010 12 1010 12 1010 12 1010 5 1010 7 1010 5 1020 22 1020 13 1030 10 This is what I want the result to look like. 1010 12 5 7 1020 22 13 1030 10 Can you help, please? "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#57
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Yes...that would be great.
"T. Valko" wrote: This is what I want the result to look like. 1010 12 5 7 1020 22 13 1030 10 I can get the result to look like this: 1010...5...7...12 1020...13...22 1030...10 Do you want to go with that? -- Biff Microsoft Excel MVP "jbf" wrote in message ... Hi Biff, I've read quite a few of the posts on returning multiple corresponding values but everything is basically over my head. I don't have any training or exp in this stuff. I've tried to replicate the example at: http://office.microsoft.com/en-us/ex...0551033#Remove I was able to return only one value for ashish 234. I couldn't get the formula to return 534 or 834. Here's the formula I have. =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2) Even if I get this right, I'm not sure if it is what I need. I have a data set where one value can have multiple corresponding values. It's not a 1:1 relationship. I want all values returned but only the distinct values. Here's an example of the data. Acct Line 1010 12 1010 12 1010 12 1010 12 1010 12 1010 5 1010 7 1010 5 1020 22 1020 13 1030 10 This is what I want the result to look like. 1010 12 5 7 1020 22 13 1030 10 Can you help, please? "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#58
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
OK....
Assume your data is in the range A2:B12. Create these named ranges: InsertNameDefine Name: Acct Refers to: =$A$2:$A$12 Name: Line Refers to: =$B$2:$B$12 List the unique Acct numbers in a range of cells. Assume they're in the range D2:D4. You can do this several ways: type them in manually, use a formula, or use the advanced filter to extract the uniques and copy to a new location. Enter this array formula** in E2: =MIN(IF(Acct=D2,Line)) Copy down to D4 Enter this array formula** in F2: =IF(COLUMNS($E2:F2)<=COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line)),MIN(IF((Acct=$D 2)*(LineE2),Line)),"") Copy down to F4. Select the range F2:F4 and copy across until you get a solid column full of blanks. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. This portion of the formula can be calculation intensive if you have a large amount of data: COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line)) You can place this as a separate formula in a helper cell and then refer to that cell instead of using it in every formula. That formula returns the count of unique line numbers per acct. This is used as an error trap so that when the formula is copied across and as it "runs out of data" it will return a blank rather than an error. -- Biff Microsoft Excel MVP "jbf" wrote in message ... Yes...that would be great. "T. Valko" wrote: This is what I want the result to look like. 1010 12 5 7 1020 22 13 1030 10 I can get the result to look like this: 1010...5...7...12 1020...13...22 1030...10 Do you want to go with that? -- Biff Microsoft Excel MVP "jbf" wrote in message ... Hi Biff, I've read quite a few of the posts on returning multiple corresponding values but everything is basically over my head. I don't have any training or exp in this stuff. I've tried to replicate the example at: http://office.microsoft.com/en-us/ex...0551033#Remove I was able to return only one value for ashish 234. I couldn't get the formula to return 534 or 834. Here's the formula I have. =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2) Even if I get this right, I'm not sure if it is what I need. I have a data set where one value can have multiple corresponding values. It's not a 1:1 relationship. I want all values returned but only the distinct values. Here's an example of the data. Acct Line 1010 12 1010 12 1010 12 1010 12 1010 12 1010 5 1010 7 1010 5 1020 22 1020 13 1030 10 This is what I want the result to look like. 1010 12 5 7 1020 22 13 1030 10 Can you help, please? "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#59
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Hello,
Another approach: Select a range of 6 rows and 2 columns and array-enter: =Pfreq(A1:A11,B1:B11) Pfreq you can find he http://www.sulprobil.com/html/pfreq.html Regards, Bernd |
|
#60
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Hi Biff,
I am following this thread and use your example. My formula is: {=INDEX(Customer,SMALL(IF(Revenue*$C$3Criteria,RO W(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))} I got what I want, but now I face a new problem. I want those values whose status is not 'under negotiation'. I tried {=INDEX(Customer,SMALL(IF((Status<"under negotiation")*(Revenue*$C$3Criteria),ROW(INDIRECT ("1:"&ROWS(Customer)))),ROW(A1)))} But was return #N/A. Is there anything I can do to rectify this? Thanks "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#61
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
I managed to solve the issue. Thanks
":)" wrote: Hi Biff, I am following this thread and use your example. My formula is: {=INDEX(Customer,SMALL(IF(Revenue*$C$3Criteria,RO W(INDIRECT("1:"&ROWS(Customer)))),ROW(A1)))} I got what I want, but now I face a new problem. I want those values whose status is not 'under negotiation'. I tried {=INDEX(Customer,SMALL(IF((Status<"under negotiation")*(Revenue*$C$3Criteria),ROW(INDIRECT ("1:"&ROWS(Customer)))),ROW(A1)))} But was return #N/A. Is there anything I can do to rectify this? Thanks "Biff" wrote: You can use dynamic ranges that automatically adjust as the size of your data set changes. See instructions he http://contextures.com/xlNames01.html#Dynamic Then the formula would be something like this: (array entered) =INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1))) Biff "gfactor" wrote in message ... Biff, I'm using your formula: =INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1))) My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named ranges with variable amounts of data? I export data out of our accounting and want to use the formula, but the number of data points changes. It seems that the function only works if all the cells between $b$1 and $b$190 have data. Am i missing something? thanks in advance, greg "Biff" wrote: Hi! this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. The sheet does not need to be sorted and it doesn't matter if there are dupe return values. Post the *EXACT* formula that you tried. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. have you got anything else? Pivot table or filter Biff "MetricsShiva" wrote in message ... this formula works if the sheet is sorted by the value i'm looking up and if there are no duplicates in the field I want returned. Otherwise i get either incorrect values returned or errors.. basically, i have a sheet listing jobs scheduled by managers. I want to be able to look up the manager's name and return a list of all the job's scheduled and the dates they were scheduled on. I then want to include this in a weekly dashboard for the 50+ managers i'm monitoring. Thanks for the response, but have you got anything else? "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#62
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Awesome, I have been searching for this... How do i mod it for to return a -
etc if there is an error. Such as it has checked the list and returned everything but i have a defined range. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#63
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
How about providing some specific details about what you're wanting to do.
-- Biff Microsoft Excel MVP "Chris" wrote in message ... Awesome, I have been searching for this... How do i mod it for to return a - etc if there is an error. Such as it has checked the list and returned everything but i have a defined range. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#64
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
When I drag it down and fill in the cells, i get #NUM as it cannot locate any
more matches. How do i ISERROR that out to return a "-" after it meets the end threshold. "T. Valko" wrote: How about providing some specific details about what you're wanting to do. -- Biff Microsoft Excel MVP "Chris" wrote in message ... Awesome, I have been searching for this... How do i mod it for to return a - etc if there is an error. Such as it has checked the list and returned everything but i have a defined range. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? . |
|
#65
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
The generic method is like this:
=IF(ISERROR(SMALL(IF(A$1:A$10=lookup_value,ROW($1: $10)),ROW(1:1))),"",INDEX(B$1:B$10,SMALL(IF(A$1:A$ 10=lookup_value,ROW($1:$10)),ROW(1:1)))) However, that's not very efficient or robust. If you provide some details we can come up with something that's better. -- Biff Microsoft Excel MVP "Chris" wrote in message ... When I drag it down and fill in the cells, i get #NUM as it cannot locate any more matches. How do i ISERROR that out to return a "-" after it meets the end threshold. "T. Valko" wrote: How about providing some specific details about what you're wanting to do. -- Biff Microsoft Excel MVP "Chris" wrote in message ... Awesome, I have been searching for this... How do i mod it for to return a - etc if there is an error. Such as it has checked the list and returned everything but i have a defined range. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? . |
|
#66
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
is there a way to modify this formula to match 2 values and return multiple
corresponding values? i need column a = x and column b = y and then return results. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#67
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
i need column a = x and column b = y and then return results.
Return what results? From where? -- Biff Microsoft Excel MVP "eec" wrote in message ... is there a way to modify this formula to match 2 values and return multiple corresponding values? i need column a = x and column b = y and then return results. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? |
|
#68
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
sorry for the lack of detail. i am pulling info from one worksheet with all
of our invoice information and trying to make a report that only shows the invoices that match 2 criteria. so if a row of data contains both x and y then i want it to show on the report. which for now is just a separate tab in the same workbook. from the previous posts in this string i can pull data that matches one criteria i just don't know how or if i can expand it to match two criteria. thanks "T. Valko" wrote: i need column a = x and column b = y and then return results. Return what results? From where? -- Biff Microsoft Excel MVP "eec" wrote in message ... is there a way to modify this formula to match 2 values and return multiple corresponding values? i need column a = x and column b = y and then return results. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? . |
|
#69
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
Here's a small sample file that demonstrates this.
EEC.xls 19kb http://cjoint.com/?cdr7UrLcDS -- Biff Microsoft Excel MVP "eec" wrote in message ... sorry for the lack of detail. i am pulling info from one worksheet with all of our invoice information and trying to make a report that only shows the invoices that match 2 criteria. so if a row of data contains both x and y then i want it to show on the report. which for now is just a separate tab in the same workbook. from the previous posts in this string i can pull data that matches one criteria i just don't know how or if i can expand it to match two criteria. thanks "T. Valko" wrote: i need column a = x and column b = y and then return results. Return what results? From where? -- Biff Microsoft Excel MVP "eec" wrote in message ... is there a way to modify this formula to match 2 values and return multiple corresponding values? i need column a = x and column b = y and then return results. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? . |
|
#70
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
you are my hero. thanks so much for the help!!
"T. Valko" wrote: Here's a small sample file that demonstrates this. EEC.xls 19kb http://cjoint.com/?cdr7UrLcDS -- Biff Microsoft Excel MVP "eec" wrote in message ... sorry for the lack of detail. i am pulling info from one worksheet with all of our invoice information and trying to make a report that only shows the invoices that match 2 criteria. so if a row of data contains both x and y then i want it to show on the report. which for now is just a separate tab in the same workbook. from the previous posts in this string i can pull data that matches one criteria i just don't know how or if i can expand it to match two criteria. thanks "T. Valko" wrote: i need column a = x and column b = y and then return results. Return what results? From where? -- Biff Microsoft Excel MVP "eec" wrote in message ... is there a way to modify this formula to match 2 values and return multiple corresponding values? i need column a = x and column b = y and then return results. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? . . |
|
#71
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to return mulitple corresponding values
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "eec" wrote in message ... you are my hero. thanks so much for the help!! "T. Valko" wrote: Here's a small sample file that demonstrates this. EEC.xls 19kb http://cjoint.com/?cdr7UrLcDS -- Biff Microsoft Excel MVP "eec" wrote in message ... sorry for the lack of detail. i am pulling info from one worksheet with all of our invoice information and trying to make a report that only shows the invoices that match 2 criteria. so if a row of data contains both x and y then i want it to show on the report. which for now is just a separate tab in the same workbook. from the previous posts in this string i can pull data that matches one criteria i just don't know how or if i can expand it to match two criteria. thanks "T. Valko" wrote: i need column a = x and column b = y and then return results. Return what results? From where? -- Biff Microsoft Excel MVP "eec" wrote in message ... is there a way to modify this formula to match 2 values and return multiple corresponding values? i need column a = x and column b = y and then return results. "Biff" wrote: Hi! The basic formula is something like this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1))) Then copy down. Where column A contains the lookup_value and column B contains the values to be returned. Need more specific details to offer a more robust suggestion. Biff "MetricsShiva" wrote in message ... i want to look up a name that occurs several times in one column of a spreadsheet and return corresponding values from each row the name occurs on. Vlookup returns only one value. How can I get multiple values? . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
| Return range of values on an "IF" statement | Excel Worksheet Functions | |||
| How do I return the cell address of the largest of a set of values | Excel Discussion (Misc queries) | |||
| Adding multiple cells, return specific values | Excel Worksheet Functions | |||
| Lookup values in a list and return multiple rows of data | Excel Worksheet Functions | |||
Linear Mode
