About Us

nikko

Sorry .. didnt get wad you mean...
--
nikko

"T. Valko" wrote:

Refresh my memory.

--
Biff
Microsoft Excel MVP

"nikko" wrote in message
...
Hi Biff

Using below formula and its returning the values not in sequence

=INDEX('Data3-Opp Activities Info'!H$1:H$13,SMALL(IF('Data3-Opp Activities
Info'!A$1:A$13=$J$2,ROW($1:$13)),ROW(1:1)))

is there a way to return the corresponding values in sorted order i.e. w
the
most recent activity showing up first?

Thanks you!

--
nikko

"Biff" wrote:

in the formula, it looks like were mutliplying the rng2
valule by the corresponding value in rng3, however
the rng2 value is not a number. it is in most cases
text. not sure if that is the problem.

No, it's not a problem. It's not actually multiplying numbers. It's
multiplying logical tests that return either TRUE or FALSE. For example:

('PO Detail'!B$1:B$500=$B$3)*('PO Detail'!K$1:K$5000)

This will return an array like this:

(TRUE)*(TRUE)
(TRUE)*(FALSE)
(FALSE)*(TRUE)
(FALSE)*(FALSE)

These logicals are multiplied together and result in an array of 1's or
0's
where 1 evalautes to the value_if_true and 0 evaluates to the
value_if_false. The value_if_true is a number from 1 to 500 that's
derived
from this expression:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

When the value_if_true condition is met then the corresponding numbers
from
that expression are then passed to the Small function.

..here is my exact formula (Array entered)
=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

If you're not using dynamic named ranges then I would "dummy down" this
portion:

ROW(INDIRECT("1:"&ROWS('PO Detail'!B$1:B$500))

To:

ROW($1:$500)

You don't actually need the sheet name or the cell references but you'll
see
where some people use them just for a better understanding.

Biff

"gfactor" wrote in message
...
biff,

thanks for your help...getting an error. here is what you recommended:

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

..here is my exact formula (Array entered)

=INDEX('PO Detail'!D$1:D$500,SMALL(IF(('PO Detail'!B$1:B$500=$B$3)*('PO
Detail'!K$1:K$5000),ROW(INDIRECT("1:"&ROWS('PO
Detail'!B$1:B$500)))),ROW('PO
Detail'!B18)))

its still bringing back values from range 2 if range 3 is 0. in the
formula, it looks like were mutliplying the rng2 valule by the
corresponding
value in rng3, however the rng2 value is not a number. it is in most
cases
text. not sure if that is the problem. maybe we can just check to
see
if
rng3 is 0? i tried this, but syntax wasn't right. any thoughts?

thanks in advance,

g

"Biff" wrote:

i only want the result delivered if the value in (rng3) is "0"

Try this: (array entered)

=INDEX(Rng2,SMALL(IF((Rng1=$z$1)*(Rng30),ROW(INDI RECT("1:"&ROWS(Rng2)))),ROW(A1)))

Biff

"gfactor" wrote in message
...
biff -

you nailed it kid. got the result i was looking for in terms of
being
able
to have a variable length array. here's my next question:

lets assume my formula reads as follows:

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

...and my data looks like this:

a(rng2) b(rng1) c (rng4)
1 y1 p1 5
2 x1 p2 1
3 y1 p3 0
4 z1 p4 3
5 q1 p5 4

if my $z$1 value is "y1"...the formula returns 2 data sets:

y1 p1
y1 p3

the thing is, i only want the result delivered if the value in
(rng3)
is
"0"

can you help me?

thanks in advance.

g

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Huge problem with "if" formula's	[email protected]	Excel Discussion (Misc queries)	16	July 20th 05 07:20 PM
Return range of values on an "IF" statement	KelleyS	Excel Worksheet Functions	0	June 1st 05 08:32 PM
How do I return the cell address of the largest of a set of values	Mr. Snrub	Excel Discussion (Misc queries)	8	May 28th 05 03:57 PM
Adding multiple cells, return specific values	Jim	Excel Worksheet Functions	4	December 8th 04 07:26 AM
Lookup values in a list and return multiple rows of data	Amanda L	Excel Worksheet Functions	2	December 2nd 04 04:48 PM

Menu

About Us