How to look up a value in a list and return multiple corresponding values

[Alex]

I am trying to use the Microsoft.com Excel instructions on how to look
up a value and return multiple values (http://office.microsoft.com/en-
us/excel/HA012260381033.aspx?pid=CL100570551033). I want it to search
an array $A$2:$B$33780 and return multiple values corresponding to
20,000 other values (in the following example it is looking for values
corresponding to the number in cell C4), but I can only get it to
return the first instance of a correponding value:

=INDEX($A$2:$B$11,SMALL(IF($A$1:$A$7=$C4,ROW($A$1:$A$7)),ROW(1:1)),2)

What should the formula look like that finds the second instance?

Thanks in advance,
Alex

 

[Ragdyer]

Try this *array* formula:

=INDEX($B$2:$B$3278,SMALL(IF($A$2:$A$3278=$C$4,ROW($A$1:$A$3277)),ROWS($1:1)
))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy down as many rows as you anticipate the number
of returns might be.

When you run out of values to return, you'll get a #NUM! error.
This tells you that you have returned *all* possible values.
If you don't see the error message, there are more values to be returned,
and you should copy the formula down additional rows.

If you do not wish to see the error message, post back for a revision to the
formula to eliminate the message.

 

[Guest]

Hello Alex,

Your ranges don't quite match and you need to make the reference to C4
absolute. If you use this formula somewhere in row 1 then copy down it should
find all matches:


=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$4,ROW($A$1:$A$7)),ROW(1:1)),2)

note: needs to be confirmed with CTRL+SHIFT+ENTER

[to do this select cell with formula, press F2 and then, while holding down
CTRL and SHIFT keys, press ENTER so that curly braces like { ans } appear
around the formula in the formula bar]

note: to prevent errors when you run out of matches you might want to amend to


=IF(COUNTIF($A$1:$A$7,$C$4)>=ROW(1:1),INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$C$4,ROW($A$1:$A$7)),ROW(1:1)),2),"")

also confirmed with CTRL+SHIFT+ENTER

 

[Alex]

Is there a better way to hide the error message than just using:

=if((INDEX($B$2:$B$3278,SMALL(IF($A$2:$A$3278=$C$4,ROW($A$1:$A
$3277)),ROWS($1:1)

)))="#NUM","",INDEX($B$2:$B$3278,SMALL(IF($A$2:$A$3278=$C$4,ROW($A$1:$A$3277)),ROWS($1:1)
)))

Thanks,
Alex

 

[Alex]

I slightly modified the formula so that it would search for a value in
Row B and return the corresponding value in Row A. However, it is now
returning several instances of the same corresponding values (ie. the
first one returns the same value in 10 rows with a few incorrect
values thrown in, but it should only return two values).

=INDEX(ORGANIZATION!$A$2:$A$3278,SMALL(IF(ORGANIZATION!$A$2:$A
$3278=ORGANIZATION!$A$3,ROW(ORGANIZATION!$B$2:$B
$3278)),ROWS(ORGANIZATION!$1:1)
))

Thanks again for all the help,
Alex

 

[RagDyeR]

Do you realize that you're matching the data in a cell (A3), that is part of
the list itself?
Nothing wrong with that, just thought it was worth mentioning.

Try this *array* formula- WITHOUT error trap:

=INDEX(Organization!$A$2:$A$3278,SMALL(IF(Organization!$B$2:$B$3278=Organization!$A$3,ROW($1:$3277)),ROWS($1:1)))

WITH error trap:

=IF(COUNTIF(Organization!B$2:B$3278,Organization!A$3)>=ROWS($1:1),INDEX(Organization!A$2:A$3278,SMALL(IF(Organization!B$2:B$3278=Organization!A$3,ROW($1:$3277)),ROWS($1:1))),"")

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I slightly modified the formula so that it would search for a value in
Row B and return the corresponding value in Row A. However, it is now
returning several instances of the same corresponding values (ie. the
first one returns the same value in 10 rows with a few incorrect
values thrown in, but it should only return two values).

=INDEX(ORGANIZATION!$A$2:$A$3278,SMALL(IF(ORGANIZATION!$A$2:$A
$3278=ORGANIZATION!$A$3,ROW(ORGANIZATION!$B$2:$B
$3278)),ROWS(ORGANIZATION!$1:1)
))

Thanks again for all the help,
Alex

 

[Alex]

That worked great! Thanks so much, when I try to autofill the array
it stops on column Q (out of Z) on row 2848 which comes out to 65472
arrays. What is the limit for one spreadsheet?

Thanks!
Alex

 

[Ragdyer]

Now you've lost me completely!?!?!

What are you doing in Column Q??? ... And on Row 2848 ???

You said you're looking for a single value, and want to return all matching
occurrences in an adjoining column, in the same row!

Kind of late in this thread to throw curve balls.