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 |
#11
|
|||
|
|||
how to return mulitple corresponding values
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? |
#12
|
|||
|
|||
how to return mulitple corresponding values
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? |
#13
|
|||
|
|||
how to return mulitple corresponding values
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? |
#14
|
|||
|
|||
how to return mulitple corresponding values
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? |
#15
|
|||
|
|||
how to return mulitple corresponding values
=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? |
#16
|
|||
|
|||
how to return mulitple corresponding values
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? |
#17
|
|||
|
|||
how to return mulitple corresponding values
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? |
#18
|
|||
|
|||
how to return mulitple corresponding values
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? |
#19
|
|||
|
|||
how to return mulitple corresponding values
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? |
#20
|
|||
|
|||
how to return mulitple corresponding 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 |