| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple values (INDEX?)
In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do
is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
Hello,
I think you might need something like my UDF vlookupall. See http://www.sulprobil.com/html/lookup-variants.html, for example. Regards, Bernd |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
Hi DJ,
If you name the Numbers range (equivalent to A2:A15000 on your RD sheet) and Names range (equivalent to D2:D15000 on your RD sheet). Then insert the following formula in B2 on your RS sheet: =INDEX(Names,MATCH($A2,Numbers,0)) and the following formula in C2: =IF(OR(B2="",COUNTA($B2:B2)=COUNTIF(Numbers,$A2)), "",INDEX(Names,MATCH($A2,OFFSET(Numbers,MATCH(B2,N ames,0),0,COUNT(Numbers)+1-MATCH(B2,Names,0),1),0)+MATCH(B2,Names,0))) Copy the formula in C2 across as far as you're likely to need, then copy all the formulae on row 2 down to row 986. Note: With so much data, don't expect lightning-fast recalcs. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "DJ" wrote in message ... In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
This is what the formula is right now, haven't figured it out yet.
=IF(COUNTIF('Raw Data'!$D$2:$D$15000,'Race Strength'!$A2)='Race Strength'!B$1,INDEX('Raw Data'!$A$2:$A$15000,SMALL(INDEX(('Raw Data'!$D$2:$D$15000='Race Strength'!$A2)*ROW('Raw Data'!$D$2:$D$15000)+('Raw Data'!$D$2:$D$15000<'Race Strength'!$A2)*10^99,0),'Race Strength'!B$1)),"") "Ron Coderre" wrote: If you figure it out....please post the final formula. And if you can't figure it out....again...post the formula, so we'll have something to work with. *********** Regards, Ron XL2002, WinXP "DJ" wrote: Pefect! Except that everything is off by one place. The first number doesn't return the first name that should be associated with it, but it does have the first name that goes with the second number (at the end). I'm sure it's a simple fix. Let's see if I can figure it out... "Ron Coderre" wrote: With these parameters... On sheet "RS" A2:A986 contains numeric values (Each of those numbers will exist on sheet "RD") On sheet "RD" D2:D15000 contains numeric values (Some of those values MAY NOT exist in Col_A of sheet "RS") A2:A15000 contains names associated with the Col_D numbers (There may be more than one name in Col_A associated with each number in Col_D) Col_A/Col_D combinations are unique ------------------------ Try this On sheet "RS" B1:K1 contains the series 1,2,....10 This formula returns the Row_1 referenced name associated with the value in Col_A B2: =IF(COUNTIF(RD!$D$2:$D$61,$A2)=B$1,INDEX(RD!$A$1: $A$61,SMALL(INDEX((RD!$D$2:$D$61=RS!$A2)*ROW(RD!$D $2:$D$61)+(RD!$D$2:$D$61<RS!$A2)*10^99,0),RS!B$1) ),"") Copy that formula down and to the right as far as you need. Note: In case text wrap impacts the display, that formula contains NO spaces. Sample returned values (using bogus test data) Nums 1 2 3 1 a t am 2 b u an 3 c v ao Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DJ" wrote: In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
Pefect! Except that everything is off by one place. The first number doesn't
return the first name that should be associated with it, but it does have the first name that goes with the second number (at the end). I'm sure it's a simple fix. Let's see if I can figure it out... "Ron Coderre" wrote: With these parameters... On sheet "RS" A2:A986 contains numeric values (Each of those numbers will exist on sheet "RD") On sheet "RD" D2:D15000 contains numeric values (Some of those values MAY NOT exist in Col_A of sheet "RS") A2:A15000 contains names associated with the Col_D numbers (There may be more than one name in Col_A associated with each number in Col_D) Col_A/Col_D combinations are unique ------------------------ Try this On sheet "RS" B1:K1 contains the series 1,2,....10 This formula returns the Row_1 referenced name associated with the value in Col_A B2: =IF(COUNTIF(RD!$D$2:$D$61,$A2)=B$1,INDEX(RD!$A$1: $A$61,SMALL(INDEX((RD!$D$2:$D$61=RS!$A2)*ROW(RD!$D $2:$D$61)+(RD!$D$2:$D$61<RS!$A2)*10^99,0),RS!B$1) ),"") Copy that formula down and to the right as far as you need. Note: In case text wrap impacts the display, that formula contains NO spaces. Sample returned values (using bogus test data) Nums 1 2 3 1 a t am 2 b u an 3 c v ao Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DJ" wrote: In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
I did try this one too, and it looked great until about the 40th line or so.
Then it started getting weird results. Sometimes it would give the same name for the whole line, others it would alternate the first two names back and forth for the whole line, and some would start repeating the names after the list should have been done. It was probably something I did though. Thanks anyway. "macropod" wrote: Hi DJ, If you name the Numbers range (equivalent to A2:A15000 on your RD sheet) and Names range (equivalent to D2:D15000 on your RD sheet). Then insert the following formula in B2 on your RS sheet: =INDEX(Names,MATCH($A2,Numbers,0)) and the following formula in C2: =IF(OR(B2="",COUNTA($B2:B2)=COUNTIF(Numbers,$A2)), "",INDEX(Names,MATCH($A2,OFFSET(Numbers,MATCH(B2,N ames,0),0,COUNT(Numbers)+1-MATCH(B2,Names,0),1),0)+MATCH(B2,Names,0))) Copy the formula in C2 across as far as you're likely to need, then copy all the formulae on row 2 down to row 986. Note: With so much data, don't expect lightning-fast recalcs. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "DJ" wrote in message ... In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
I believe I found the problem.
Because the "SMALL" section returns the Row Number of the matched item, the first INDEX section must begin in $A$1 not $A$2 Example: You used..... INDEX('Raw Data'!$A$2:$A$15000,SMALL( You SHOULD have used..... INDEX('Raw Data'!$A$1:$A$15000,SMALL( Does that help? *********** Regards, Ron XL2002, WinXP "DJ" wrote: This is what the formula is right now, haven't figured it out yet. =IF(COUNTIF('Raw Data'!$D$2:$D$15000,'Race Strength'!$A2)='Race Strength'!B$1,INDEX('Raw Data'!$A$2:$A$15000,SMALL(INDEX(('Raw Data'!$D$2:$D$15000='Race Strength'!$A2)*ROW('Raw Data'!$D$2:$D$15000)+('Raw Data'!$D$2:$D$15000<'Race Strength'!$A2)*10^99,0),'Race Strength'!B$1)),"") "Ron Coderre" wrote: If you figure it out....please post the final formula. And if you can't figure it out....again...post the formula, so we'll have something to work with. *********** Regards, Ron XL2002, WinXP "DJ" wrote: Pefect! Except that everything is off by one place. The first number doesn't return the first name that should be associated with it, but it does have the first name that goes with the second number (at the end). I'm sure it's a simple fix. Let's see if I can figure it out... "Ron Coderre" wrote: With these parameters... On sheet "RS" A2:A986 contains numeric values (Each of those numbers will exist on sheet "RD") On sheet "RD" D2:D15000 contains numeric values (Some of those values MAY NOT exist in Col_A of sheet "RS") A2:A15000 contains names associated with the Col_D numbers (There may be more than one name in Col_A associated with each number in Col_D) Col_A/Col_D combinations are unique ------------------------ Try this On sheet "RS" B1:K1 contains the series 1,2,....10 This formula returns the Row_1 referenced name associated with the value in Col_A B2: =IF(COUNTIF(RD!$D$2:$D$61,$A2)=B$1,INDEX(RD!$A$1: $A$61,SMALL(INDEX((RD!$D$2:$D$61=RS!$A2)*ROW(RD!$D $2:$D$61)+(RD!$D$2:$D$61<RS!$A2)*10^99,0),RS!B$1) ),"") Copy that formula down and to the right as far as you need. Note: In case text wrap impacts the display, that formula contains NO spaces. Sample returned values (using bogus test data) Nums 1 2 3 1 a t am 2 b u an 3 c v ao Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DJ" wrote: In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
BINGO! Thanks so much!
"Ron Coderre" wrote: I believe I found the problem. Because the "SMALL" section returns the Row Number of the matched item, the first INDEX section must begin in $A$1 not $A$2 Example: You used..... INDEX('Raw Data'!$A$2:$A$15000,SMALL( You SHOULD have used..... INDEX('Raw Data'!$A$1:$A$15000,SMALL( Does that help? *********** Regards, Ron XL2002, WinXP "DJ" wrote: This is what the formula is right now, haven't figured it out yet. =IF(COUNTIF('Raw Data'!$D$2:$D$15000,'Race Strength'!$A2)='Race Strength'!B$1,INDEX('Raw Data'!$A$2:$A$15000,SMALL(INDEX(('Raw Data'!$D$2:$D$15000='Race Strength'!$A2)*ROW('Raw Data'!$D$2:$D$15000)+('Raw Data'!$D$2:$D$15000<'Race Strength'!$A2)*10^99,0),'Race Strength'!B$1)),"") "Ron Coderre" wrote: If you figure it out....please post the final formula. And if you can't figure it out....again...post the formula, so we'll have something to work with. *********** Regards, Ron XL2002, WinXP "DJ" wrote: Pefect! Except that everything is off by one place. The first number doesn't return the first name that should be associated with it, but it does have the first name that goes with the second number (at the end). I'm sure it's a simple fix. Let's see if I can figure it out... "Ron Coderre" wrote: With these parameters... On sheet "RS" A2:A986 contains numeric values (Each of those numbers will exist on sheet "RD") On sheet "RD" D2:D15000 contains numeric values (Some of those values MAY NOT exist in Col_A of sheet "RS") A2:A15000 contains names associated with the Col_D numbers (There may be more than one name in Col_A associated with each number in Col_D) Col_A/Col_D combinations are unique ------------------------ Try this On sheet "RS" B1:K1 contains the series 1,2,....10 This formula returns the Row_1 referenced name associated with the value in Col_A B2: =IF(COUNTIF(RD!$D$2:$D$61,$A2)=B$1,INDEX(RD!$A$1: $A$61,SMALL(INDEX((RD!$D$2:$D$61=RS!$A2)*ROW(RD!$D $2:$D$61)+(RD!$D$2:$D$61<RS!$A2)*10^99,0),RS!B$1) ),"") Copy that formula down and to the right as far as you need. Note: In case text wrap impacts the display, that formula contains NO spaces. Sample returned values (using bogus test data) Nums 1 2 3 1 a t am 2 b u an 3 c v ao Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DJ" wrote: In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
If you figure it out....please post the final formula.
And if you can't figure it out....again...post the formula, so we'll have something to work with. *********** Regards, Ron XL2002, WinXP "DJ" wrote: Pefect! Except that everything is off by one place. The first number doesn't return the first name that should be associated with it, but it does have the first name that goes with the second number (at the end). I'm sure it's a simple fix. Let's see if I can figure it out... "Ron Coderre" wrote: With these parameters... On sheet "RS" A2:A986 contains numeric values (Each of those numbers will exist on sheet "RD") On sheet "RD" D2:D15000 contains numeric values (Some of those values MAY NOT exist in Col_A of sheet "RS") A2:A15000 contains names associated with the Col_D numbers (There may be more than one name in Col_A associated with each number in Col_D) Col_A/Col_D combinations are unique ------------------------ Try this On sheet "RS" B1:K1 contains the series 1,2,....10 This formula returns the Row_1 referenced name associated with the value in Col_A B2: =IF(COUNTIF(RD!$D$2:$D$61,$A2)=B$1,INDEX(RD!$A$1: $A$61,SMALL(INDEX((RD!$D$2:$D$61=RS!$A2)*ROW(RD!$D $2:$D$61)+(RD!$D$2:$D$61<RS!$A2)*10^99,0),RS!B$1) ),"") Copy that formula down and to the right as far as you need. Note: In case text wrap impacts the display, that formula contains NO spaces. Sample returned values (using bogus test data) Nums 1 2 3 1 a t am 2 b u an 3 c v ao Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DJ" wrote: In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
I'm glad that worked for you.
.......and thanks for the feedback. Much appreciated. *********** Regards, Ron XL2002, WinXP "DJ" wrote: BINGO! Thanks so much! "Ron Coderre" wrote: I believe I found the problem. Because the "SMALL" section returns the Row Number of the matched item, the first INDEX section must begin in $A$1 not $A$2 Example: You used..... INDEX('Raw Data'!$A$2:$A$15000,SMALL( You SHOULD have used..... INDEX('Raw Data'!$A$1:$A$15000,SMALL( Does that help? *********** Regards, Ron XL2002, WinXP "DJ" wrote: This is what the formula is right now, haven't figured it out yet. =IF(COUNTIF('Raw Data'!$D$2:$D$15000,'Race Strength'!$A2)='Race Strength'!B$1,INDEX('Raw Data'!$A$2:$A$15000,SMALL(INDEX(('Raw Data'!$D$2:$D$15000='Race Strength'!$A2)*ROW('Raw Data'!$D$2:$D$15000)+('Raw Data'!$D$2:$D$15000<'Race Strength'!$A2)*10^99,0),'Race Strength'!B$1)),"") "Ron Coderre" wrote: If you figure it out....please post the final formula. And if you can't figure it out....again...post the formula, so we'll have something to work with. *********** Regards, Ron XL2002, WinXP "DJ" wrote: Pefect! Except that everything is off by one place. The first number doesn't return the first name that should be associated with it, but it does have the first name that goes with the second number (at the end). I'm sure it's a simple fix. Let's see if I can figure it out... "Ron Coderre" wrote: With these parameters... On sheet "RS" A2:A986 contains numeric values (Each of those numbers will exist on sheet "RD") On sheet "RD" D2:D15000 contains numeric values (Some of those values MAY NOT exist in Col_A of sheet "RS") A2:A15000 contains names associated with the Col_D numbers (There may be more than one name in Col_A associated with each number in Col_D) Col_A/Col_D combinations are unique ------------------------ Try this On sheet "RS" B1:K1 contains the series 1,2,....10 This formula returns the Row_1 referenced name associated with the value in Col_A B2: =IF(COUNTIF(RD!$D$2:$D$61,$A2)=B$1,INDEX(RD!$A$1: $A$61,SMALL(INDEX((RD!$D$2:$D$61=RS!$A2)*ROW(RD!$D $2:$D$61)+(RD!$D$2:$D$61<RS!$A2)*10^99,0),RS!B$1) ),"") Copy that formula down and to the right as far as you need. Note: In case text wrap impacts the display, that formula contains NO spaces. Sample returned values (using bogus test data) Nums 1 2 3 1 a t am 2 b u an 3 c v ao Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DJ" wrote: In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
Thanks for the reply. I can't tell you if it worked or not, because I had no
clue how to even attempt that. :) "Bernd" wrote: Hello, I think you might need something like my UDF vlookupall. See http://www.sulprobil.com/html/lookup-variants.html, for example. Regards, Bernd |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple values (INDEX?)
With these parameters...
On sheet "RS" A2:A986 contains numeric values (Each of those numbers will exist on sheet "RD") On sheet "RD" D2:D15000 contains numeric values (Some of those values MAY NOT exist in Col_A of sheet "RS") A2:A15000 contains names associated with the Col_D numbers (There may be more than one name in Col_A associated with each number in Col_D) Col_A/Col_D combinations are unique ------------------------ Try this On sheet "RS" B1:K1 contains the series 1,2,....10 This formula returns the Row_1 referenced name associated with the value in Col_A B2: =IF(COUNTIF(RD!$D$2:$D$61,$A2)=B$1,INDEX(RD!$A$1: $A$61,SMALL(INDEX((RD!$D$2:$D$61=RS!$A2)*ROW(RD!$D $2:$D$61)+(RD!$D$2:$D$61<RS!$A2)*10^99,0),RS!B$1) ),"") Copy that formula down and to the right as far as you need. Note: In case text wrap impacts the display, that formula contains NO spaces. Sample returned values (using bogus test data) Nums 1 2 3 1 a t am 2 b u an 3 c v ao Is that something you can work with? *********** Regards, Ron XL2002, WinXP "DJ" wrote: In one worksheet (RS), I have a list of numbers (A2:A:986). What I want to do is search another sheet (RD) for every name (A2:A15000) that is associated with each number (D2:D15000) and have it returned to the original sheet (RS) on the same row as the number, but with each name occupying it's own cell (B2, C2, D2, etc...) I've been looking around to see if I can find a similar question and come up with my own formula, but haven't been able to figure it out yet. It kind of looks like I'd need to use INDEX, but I haven't used that one before and I'm stuck. Hopefully, I explained that adequately. Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
| Returning MULTIPLE values with Index and Match | Excel Discussion (Misc queries) | |||
| Index function using multiple values in one cell | Excel Worksheet Functions | |||
| match/index using multiple values | Excel Worksheet Functions | |||
| return multiple corresponding values using INDEX | Excel Worksheet Functions | |||
Linear Mode
