array formulas and index lookup
 

Ive got some data starting in A1

A B C D E F G H
D N O N O D D D

ie shift workers

={(B1:B8="N")}
gives me an array of true/falses

Id like an array that returns the names of those who match the criteria
ie {B, D)
I can do it with a series of individual INDEX( MATCH ...) formulae

can i do it with an array formula?
can it be done with a oneliner?

array formulas and index lookup
 

Hi
For a formula approach: As HLOOKUP only return ONE match you'll
probably need VBA for this. One way
- download Alan Beban's array functions (http://home.pacbell.net/beban)
They'll include a function called HLOOKUPS (for returning multiple
lookup results)

--
Regards
Frank Kabel
Frankfurt, Germany

"p cooper" schrieb im Newsbeitrag
om...
Ive got some data starting in A1

A B C D E F G H
D N O N O D D D

ie shift workers

={(B1:B8="N")}
gives me an array of true/falses

Id like an array that returns the names of those who match the
criteria
ie {B, D)
I can do it with a series of individual INDEX( MATCH ...) formulae

can i do it with an array formula?
can it be done with a oneliner?

array formulas and index lookup
 

I'm not exactly sure of what you need, but would this work? If you give
your first row a name like rng1, and the second row a name like rng2,
another Array formula (of the same size) might be like this...

{=IF(Rng2="n",Rng1,"")}

It should only display a "B" & "D". Not sure if you want a vba solution
though
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"p cooper" wrote in message
om...
Ive got some data starting in A1

A B C D E F G H
D N O N O D D D

ie shift workers

={(B1:B8="N")}
gives me an array of true/falses

Id like an array that returns the names of those who match the criteria
ie {B, D)
I can do it with a series of individual INDEX( MATCH ...) formulae

can i do it with an array formula?
can it be done with a oneliner?

array formulas and index lookup
 

Lookup will return an array.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Hi
For a formula approach: As HLOOKUP only return ONE match you'll
probably need VBA for this. One way
- download Alan Beban's array functions (http://home.pacbell.net/beban)
They'll include a function called HLOOKUPS (for returning multiple
lookup results)

--
Regards
Frank Kabel
Frankfurt, Germany

"p cooper" schrieb im Newsbeitrag
om...
Ive got some data starting in A1

A B C D E F G H
D N O N O D D D

ie shift workers

={(B1:B8="N")}
gives me an array of true/falses

Id like an array that returns the names of those who match the
criteria
ie {B, D)
I can do it with a series of individual INDEX( MATCH ...) formulae

can i do it with an array formula?
can it be done with a oneliner?

array formulas and index lookup
 

Hi Tom
alway forget this function :-)
thanks for the addition

--
Regards
Frank Kabel
Frankfurt, Germany


Tom Ogilvy wrote:
Lookup will return an array.


"Frank Kabel" wrote in message
...
Hi
For a formula approach: As HLOOKUP only return ONE match you'll
probably need VBA for this. One way
- download Alan Beban's array functions
(http://home.pacbell.net/beban) They'll include a function called
HLOOKUPS (for returning multiple lookup results)

--
Regards
Frank Kabel
Frankfurt, Germany

"p cooper" schrieb im Newsbeitrag
om...
Ive got some data starting in A1

A B C D E F G H
D N O N O D D D

ie shift workers

={(B1:B8="N")}
gives me an array of true/falses

Id like an array that returns the names of those who match the
criteria ie {B, D)
I can do it with a series of individual INDEX( MATCH ...) formulae

can i do it with an array formula?
can it be done with a oneliner?

array formulas and index lookup
 

I'm not exactly sure of what you need, but would this work? If you give
your first row a name like rng1, and the second row a name like rng2,
another Array formula (of the same size) might be like this...

{=IF(Rng2="n",Rng1,"")}

It should only display a "B" & "D". Not sure if you want a vba solution
though
thanks - been on holiday

This formula returns an array where the positive matches are displayed
and the negatives are a blank string, or 'FALSE'
So I now need to strip out the FLASE/blanks and then copy the formula
so the 2 poitives matches are displayed in adjacent cells.

just a matter of.......................