Trying to get results from multiple columns

[menteith]

So, the exact situation is: I’ve got a list of common names and taxonomic info (order, family, etc.) in Columns A-F (with common names in Column A), and another (shorter) list of common names in Column G. All of the names in G are in A. Both lists are in the correct (taxonomic) order, and need to stay that way.

I want to be able to search for the values in G out of the list in A, and then have it give me the whole shebang (A-F) in Column “H-L.” I really don’t care if this is exactly where it ends up; it’s going to be cut and pasted from here, so I need it to give me actual values, not just functions in the cells. So for instance, “Turkey Vulture” in G4 would give H4:“Cathartiformes” I4:“Cathartidae” J4:"[empty]" K4:“Cathartes” L4:“Cathartes aura” rather than =MATCH() or =VLOOKUP() in the cells.

I’ve tried VLOOKUP and MATCH, but neither one seems to work unless I reference the list in Column G for output, which of course doesn’t work past the common name.

[Lupp]

Do you want to assure that each “common” name will only occur once within the space of taxonomic points? And is also assured that there cannot be two common names associated with just one taxonomic item?
If so: Is this a realistic case?
If not so: How are additional occurrences/name forking treated?
“… so I need it to give me actual values, not just functions in the cells.” doesn’t seem consistent to me with the statement that you tried functions.

[mark_t]

If you are going to copy the filtered data to some other location then there is no need to use formula. Instead you can use the Advanced filter.

Select the table of columns A-F. Then from the menu choose “Data”, “More Filters”, “Advanced Filters…” and then for the filter criteria select the list in column G and click OK.

You can then copy the now filtered data and paste it to a target range of cells.

Reset the filter on your table using “Data”, “More Filters”, “Reset Filter”.

Note the heading for column G should be the same as the heading for the table column to be filtered.

[mariosv]

In the advanced filter options it’s also possible to select a range where to copy the filtered data.

[adam1969in]

Enter this formula in H4 and drag across upto L4

=VLOOKUP($G4,$A:$F,COLUMN(B1),0)