Ask Your Question

Trying to get results from multiple columns [closed]

asked 2016-10-16 02:07:23 +0100

menteith gravatar image

updated 2020-08-18 13:27:40 +0100

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-18 13:28:12.418115


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.

Lupp gravatar imageLupp ( 2016-10-22 15:56:08 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2016-10-16 02:59:14 +0100

mark_t gravatar image

updated 2016-10-16 03:02:45 +0100

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.

edit flag offensive delete link more


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

m.a.riosv gravatar imagem.a.riosv ( 2016-10-16 16:49:33 +0100 )edit

answered 2016-10-22 13:48:51 +0100

adam1969in gravatar image

Enter this formula in H4 and drag across upto L4

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-10-16 02:07:23 +0100

Seen: 83 times

Last updated: Oct 22 '16