A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to return mulitple corresponding values



 
 
Thread Tools Display Modes
  #61  
Old January 26th, 2010, 05:50 PM posted to microsoft.public.excel.worksheet.functions
chris
external usenet poster
 
Posts: 2,039
Default 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  
Old January 26th, 2010, 06:02 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old January 26th, 2010, 07:49 PM posted to microsoft.public.excel.worksheet.functions
chris
external usenet poster
 
Posts: 2,039
Default 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  
Old January 26th, 2010, 08:15 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old February 2nd, 2010, 11:10 PM posted to microsoft.public.excel.worksheet.functions
EEC
external usenet poster
 
Posts: 7
Default 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  
Old February 3rd, 2010, 01:06 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old February 3rd, 2010, 04:31 PM posted to microsoft.public.excel.worksheet.functions
EEC
external usenet poster
 
Posts: 7
Default 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  
Old February 3rd, 2010, 06:01 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old February 4th, 2010, 10:28 PM posted to microsoft.public.excel.worksheet.functions
EEC
external usenet poster
 
Posts: 7
Default 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  
Old February 4th, 2010, 11:03 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 04:45 PM


All times are GMT +1. The time now is 11:33 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.