If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
how to return mulitple corresponding values
did you get the speadsheet I sent?
"Biff" wrote: Why don't you send me your file so I can see what your trying to do! Let me know how to contact you. Biff "Debi H" wrote in message ... Do you have code to do this dynamic? and loop inside of another llop? "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? |
#22
|
|||
|
|||
how to return mulitple corresponding values
did you get the speadsheet I sent?
Yes, I'll take a look at it tonight. Biff "Debi H" wrote in message ... did you get the speadsheet I sent? "Biff" wrote: Why don't you send me your file so I can see what your trying to do! Let me know how to contact you. Biff "Debi H" wrote in message ... Do you have code to do this dynamic? and loop inside of another llop? "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? |
#23
|
|||
|
|||
how to return mulitple corresponding values
|
#24
|
|||
|
|||
how to return mulitple corresponding values
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 news 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? |
#25
|
|||
|
|||
how to return mulitple corresponding values
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 news 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? |
#26
|
|||
|
|||
how to return mulitple corresponding values
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 news 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? |
#27
|
|||
|
|||
how to return mulitple corresponding values
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 news 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? |
#28
|
|||
|
|||
how to return mulitple corresponding values
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$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 news 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? |
#29
|
|||
|
|||
how to return mulitple corresponding values
test
"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 news 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? |
#30
|
|||
|
|||
how to return mulitple corresponding values
Morning Biff. Thanks for the reply. Getting and error. Here's what you
recommended: =INDEX(Rng2,SMALL(IF((Rng1=$Z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1))) this is my exact formula (array entered): =INDEX('PO Detail'!D$1$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'!B87))) it looks like we're mutiplying rng3 x corresponding value in rng1. one of the issues is rng1 is text. is there a way to just check to see if rng3 is greater than "0". i tried, but now luck. any thoughts? 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 news 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Return Single Instance of Numeric Values from a Column | Sam via OfficeKB.com | Worksheet Functions | 4 | August 26th, 2005 03:10 AM |
return all values | turkey | New Users | 1 | May 5th, 2005 04:27 PM |
Using a Vlookup to return values in a data list? | rtjeter | Worksheet Functions | 2 | April 26th, 2005 05:56 AM |
return random number of values | hgrove | Worksheet Functions | 2 | July 9th, 2004 07:54 PM |
Search columns and rows for values to return common value | Dale | Worksheet Functions | 2 | December 18th, 2003 03:45 PM |