| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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....................... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use array for lookup value, to return array of lookups | Excel Discussion (Misc queries) | |||
| Lookup/Array Formulas | Excel Worksheet Functions | |||
| table, index, array, match, lookup? | Excel Worksheet Functions | |||
| V-Lookup, column index number indicator in table array | Excel Worksheet Functions | |||
| Index/ Lookup formulas and fuzzy matching | Excel Worksheet Functions | |||
Hybrid Mode
