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 |
#61
|
|||
|
|||
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? |
#62
|
|||
|
|||
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? |
#63
|
|||
|
|||
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? . |
#64
|
|||
|
|||
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? . |
#65
|
|||
|
|||
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? |
#66
|
|||
|
|||
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? |
#67
|
|||
|
|||
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? . |
#68
|
|||
|
|||
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? . |
#69
|
|||
|
|||
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? . . |
#70
|
|||
|
|||
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? . . |
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 |