Multiple values (INDEX?)

D

DJ

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.
 
M

macropod

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,Names,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
 
D

DJ

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)),"")
 
D

DJ

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...
 
D

DJ

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 said:
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,Names,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 said:
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.
Click to expand...
Click to expand...
 
R

Ron Coderre

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
 
D

DJ

BINGO! Thanks so much!

Ron Coderre said:
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
Click to expand...
 
R

Ron Coderre

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
 
R

Ron Coderre

I'm glad that worked for you.
.......and thanks for the feedback. Much appreciated.


***********
Regards,
Ron

XL2002, WinXP
 
D

DJ

Thanks for the reply. I can't tell you if it worked or not, because I had no
clue how to even attempt that. :)
 
R

Ron Coderre

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
 
Top