Find values in table and return correpsonding data in array

[Guest]

I'm not sure if you can do this in an array function or if it takes programming, but I am trying to search a database for multiple entries and display corresponding data separately in a new table. For example,

1 Red
2 Blue
1 Green
3 Yellow

I would like to create a new table that pulls out the data in column 2 if column 1 equals "1", like this...

Red
Green

Does anyone know if I can do this? I've tried VLOOKUP, INDEX & MATCH, and OFFSET & MATCH. Thank you.

 

[Frank Kabel]

Hi
some ways:
- if this is a one-time operation you may use 'Advanced Filters' (Menu:
'Data - Filter'). You can choose a different worksheet as target
location for your output

- A formula/UDF approach: Download Alan Beban's array functions
(http://home.pacbell.net/beban)
They'll include a function called VLOOKUPS (for returning multiple
lookup results)

- you can create a macro for this copying all relevant records to a new
sheet. Ron has some nice examples for this:
http://www.rondebruin.nl/copy5.htm

So now you have the choice. Please post back if you have further
questions