If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
how to return mulitple corresponding values
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook you might consider something like =VLookups(lookup_value,lookup_table,return_value_c olumn) array entered into a column long enough to accommodate the number of occurrences of lookup_value. Alan Beban MetricsShiva wrote: 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,R OW($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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
Hey Biff, i've got it working now. the first formula below is the one that
works... i removed the row reference numbers in the first reference to the array... "=INDEX('Cancel Push compiled'!$A:$W,SMALL(IF('Cancel Push compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push compiled'!$A$2:$A$82)),ROW(1:1)),11)" This is the formula with the row references... i can't understand why this one doesn't work.... "=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push compiled'!$A$2:$A$82)),ROW(1:1)),11)" Thank you so much!! "Metrics" "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? |
#7
|
|||
|
|||
This is the formula with the row references... i can't understand why this
one doesn't work.... "=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push compiled'!$A$2:$A$82)),ROW(1:1)),11)" The problem is he ROW('Cancel Push compiled'!$A$2:$A$82) The INDEX function is used to hold the array A2:W82. The actual size of this array is 81 elements. Whe A2:W2 = element 1 A3:W3 = element 2 A4:W4 = element 3 ... A82:W82 = element 81 The first call to the ROW function is used to specify which element to return from the INDEXED array. Since the elements in INDEX are "numbered" starting from 1, so too must the reference used inside the ROW function. If the the refernces are mismatched the results you get can and will be incorrect. (unless you have dumb luck on your side!) So: ROW('Cancel Push compiled'!$A$2:$A$82) should be written as: ROW('Cancel Push compiled'!$A$1:$A$81) Another thing, you don't need the sheet name or the columns because you're not actually referencing any physical location. The ROW function is just a means to return an array of numbers equal to the size of the INDEXED array. ROW($1:$81) Here's another way to look at it: Assume the indexed range was A247:W327. This array STILL contains 81 elements so: =INDEX(A247:W327,............................ROW($ 1:$81)...............) This is usually where people make mistakes with type of formula. Once you understand how it works, it's a very simple formula. Biff "MetricsShiva" wrote in message ... Hey Biff, i've got it working now. the first formula below is the one that works... i removed the row reference numbers in the first reference to the array... "=INDEX('Cancel Push compiled'!$A:$W,SMALL(IF('Cancel Push compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push compiled'!$A$2:$A$82)),ROW(1:1)),11)" This is the formula with the row references... i can't understand why this one doesn't work.... "=INDEX('Cancel Push compiled'!$A2:$W82,SMALL(IF('Cancel Push compiled'!$A$2:$A$82=Sheet1!$A$2,ROW('Cancel Push compiled'!$A$2:$A$82)),ROW(1:1)),11)" Thank you so much!! "Metrics" "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? |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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? |
#10
|
|||
|
|||
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 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 |