How To: Look Up Tables

I helped @chris_1974 out recently adding look up tables into an excel document.  I did promise then to do a How-To on it, and here it is.

I have put together a google spreadsheet that goes through it in step-by-step, but here are the instructions!
Step 1 – add names.
Step 2 – Set up the sheet.  In the example I have put in, there are two assessments and one homework. It is always much easier if you know what the sheet needs to look like before you start.  The look-up tables at the bottom are the grades. At this stage we haven’t done any formulae.
Step 3 – Add the data. Once you have your student’s scores in the table, you can add percentages (not important for vlookup, just FYI). Note, I the formula I have put in =C2/$C$9 has two dollar signs.  If you put the dollar signs around the letter of the cell that is going to stay constant, you can drag the formula down to the rest of the cells.
Step 4 – Once the percentages are in, start on the grades.  In the same way as I added dollar signs earlier, they become crucial here.  The formula for the first cell is: =VLOOKUP(C2,$C$12:$D$18,2).  What does this mean?  C2 – is Adam’s score. C12:D18 is the data range we want to look up, and the 2 is the column you want to return in your cell.  Once you have the first cell done, you can copy the formula down to the rest of the cells.  Remember, when you are copying it to the ‘homework’ column, you will need to reset where you want the formula to calculate or it will give you duff results!
That is it!
If there are any other areas that I can help with, shout – more than happy to expand on the above!

2 thoughts on “How To: Look Up Tables

  1. Again, thankyou. The main thing with I was having issues is the difference b/n lookup, which I’ve used for years, and vlookup. Now I know that with vlookups the columns need to be adjacent. This is a very helpful how to, now all I need to do is change ALL my lookups to vlookup.I nay be some time!

  2. 😉 no probs. There are some other funky things I use all the time in excel like concatenate and cell delimiting that I will post later!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s