View Single Post
  #36   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JB JB is offline
external usenet poster
 
Posts: 115
Default how to return mulitple corresponding values

Looked at some of you responses regarding excel and you seem very
knowledgeable. Have a question involving a lookup.



Scenario

Two Sheets



Sheet one

Has an invoice number on it.





Sheet two

Has a check number that may or may not have paid multiple invoices.

It is listed this way.



Column 1 column 2 column 3
et all

Check number invoice number invoice number







What I would like to do is use a lookup on sheet one that checks sheet two
for the invoice number (separate invoice numbers are located in columns B
through whatever) and returns what check it was paid on (checks located in
the left most column).







Cant figure out an easy way to do it and was wondering if you had any ideas.





Regards,

jb

"Biff" wrote:

You can use dynamic ranges that automatically adjust as the size of your
data set changes.

See instructions he

http://contextures.com/xlNames01.html#Dynamic

Then the formula would be something like this: (array entered)

=INDEX(Rng2,SMALL(IF(Rng1=$D$1,ROW(INDIRECT("1:"&R OWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
Biff,

I'm using your formula:

=INDEX($B$1:$B$190,SMALL(IF($A$1:$A$190=$D$1,ROW($ A$1:$A$190)),ROW(A1)))

My question is this: Can the ranges $B$1:$B$190 and A$1:$A$190 be named
ranges with variable amounts of data? I export data out of our accounting
and want to use the formula, but the number of data points changes. It
seems
that the function only works if all the cells between $b$1 and $b$190 have
data. Am i missing something?

thanks in advance,

greg

"Biff" wrote:

Hi!

this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned.

The sheet does not need to be sorted and it doesn't matter if there are
dupe
return values.

Post the *EXACT* formula that you tried.

I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?

Pivot table or filter

Biff

"MetricsShiva" wrote in message
...
this formula works if the sheet is sorted by the value i'm looking up
and
if
there are no duplicates in the field I want returned. Otherwise i get
either
incorrect values returned or errors..

basically, i have a sheet listing jobs scheduled by managers. I want
to
be
able to look up the manager's name and return a list of all the job's
scheduled and the dates they were scheduled on. I then want to include
this
in a weekly dashboard for the 50+ managers i'm monitoring.

Thanks for the response, but have you got anything else?



"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?