I recently got asked to help with setting up a formula that would autofill standard entries for particular species. The idea would be that the user would enter a species name and then the other details would fill in as normal. While the example I’ve given here is for scientific purposes, you could also adapt it to similar scenarios where you need to autofill a lot of data.
I don’t know about you but my memories of school biology are pretty vague š but it turns out that these classifications are actually pretty standard. Apparently they are only revised occasionally. So my solution was as follows:
- Enter the data on a separate sheet that has all the data – see Taxonomy sheet in attached file. I got this data from the person concerned.
- Click anywhere in this list. Convert it to a table (Ctrl and T or Insert: Table)
- Making sure you are still clicked in this list, check the Design tab on right hand side.
- On the left you will see a box that says Table1. Amend this to read Species and press Enter (to make it stick)
- Now view the Sample formulas sheet.
- Click in cell B5
- From the Formulas tab, choose Lookup & Reference
- Scroll down to end to get vlookup and enter in the following..
=VLOOKUP($B5,Species,’Taxonomy ‘!B$1,FALSE)
What it all means:
Lookup value: $B5 (this is what’s common to both lists – the item they are trying to identify). It’s set to $B5 so that it’s locked to column A so that I only have to enter the lookup value once.
Table Array: I just typed in Species here because this is the name I gave to the table in an earlier step
Column Index: B$1 – IĀ had the number of the columns entered on the Taxonomy sheet and this is what I referenced here.
False – to ensure an exact match
You can click on either of the links below to get the files with and without the formulas…
Taxonomy file without_formulas