Excel help

[Joao Corvina]

Hi all,

New to this forum! Love Excel and I explore it frequently in order to simplify my data collecting in science and to make things more practical...

I'm currently trying to find a way that whenever I input a species common name, the next cell can show up the respective latin name and the next one Family name, and so on... I've created a tab with a list of the used common names and their respective latin names, family names, genus,...

I've came to a stall! Trying to find a formula (nested or not nested) to help me do this. Could anyone kindly guide me? Not looking for answers, looking merely for guidance in the right way!

Looking forward for some help!

 

[AlanY]

a simple lookup table like this should work

Book1
	A	B	C	D	E	F	G
1		Common Name	Name1	Name2	Name3	Name4	Name5
2	Species1	Common Name1	Species1-Name1	Species1-Name2	Species1-Name3	Species1-Name4	Species1-Name5
3	Species2	Common Name2	Species2-Name1	Species2-Name2	Species2-Name3	Species2-Name4	Species2-Name5
4	Species3	Common Name3	Species3-Name1	Species3-Name2	Species3-Name3	Species3-Name4	Species3-Name5
5	Species4	Common Name4	Species4-Name1	Species4-Name2	Species4-Name3	Species4-Name4	Species4-Name5
6	Species5	Common Name5	Species5-Name1	Species5-Name2	Species5-Name3	Species5-Name4	Species5-Name5
7
8
9		Common Name	Name1	Name2	Name3	Name4	Name5
10		Common Name3	Species3-Name1	Species3-Name2	Species3-Name3	Species3-Name4	Species3-Name5
11		Common Name1	Species1-Name1	Species1-Name2	Species1-Name3	Species1-Name4	Species1-Name5
Sheet1

Cell Formulas
Range	Formula
C10:G11	C10	=VLOOKUP($B10,$B$2:$G$6,MATCH(C$9,$B$1:$G$1,0),0)

Cells with Data Validation
Cell	Allow	Criteria
B10:B11	List	=$B$2:$B$6

 

[Joao Corvina]

Thanks! I was about to reply to my own thread saying how lame I was since I didn't remember to use VLOOKUP... somehow I had in my mind that it was solely for values! Thanks!

 

[AlanY]

you're welcome