Ask Your Question
0

Trying to get results from multiple columns

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

menteith gravatar image

updated 2016-10-16 03:04:10 +0200

mark_t 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 close merge delete

Comments

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 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

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

mark_t gravatar image

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

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

Comments

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 +0200 )edit
0

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

adam1969in gravatar image

Enter this formula in H4 and drag across upto L4

=VLOOKUP($G4,$A:$F,COLUMN(B1),0)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 63 times

Last updated: Oct 22 '16