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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

On Apr 9, 10:54 am, alex....@xxxxxxxxx wrote:
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

On Apr 8, 12:43 pm, "Ragdyer" <RagD...@xxxxxxxxxxxxx> wrote:

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------<alex....@xxxxxxxxx> wrote in message

news:1176048441.917153.201860@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: error while compiling Freedos32
    ... I know that means that the symbol environ is used two times, ... That error message means exactly what it ... you have multiple *definitions* of that symbol, ... but in some header file. ...
    (comp.os.msdos.djgpp)
  • Re: 2.3.3 on Solaris 10
    ... It is quite unclear what precisely the compiler error message is ... is expanded through multiple levels of macros. ... Compile the file again: ... fragment of the preprocessor output, ...
    (comp.lang.python)
  • Multiple replies & error message I received here
    ... I regret the multiple copies of my reply here. ... I received an automated error message that the posting failed. ...
    (microsoft.public.word.newusers)
  • Re: Wireless doesnt work anymore: "Limited or no connectivity"
    ... would suspect an LSP/Winsock problem. ... linked above for multiple suggestions on this, but for your first try, ... know what to make of that error message. ...
    (microsoft.public.windowsxp.general)
  • Opening multiple files in context menu
    ... KB), filepath, creation date and/or time, a counter, parent folder, the ... when you select multiple files, the paths would be passed to the ... registry, then the first instance would collect these, the others would ...
    (microsoft.public.dotnet.languages.csharp)