Vlookup over Multiple Sheets (Issue 58) by Tiamari

If you are a regular Excel user, you are probably already using the Vlookup formula. If not, you are missing out on one of Excel’s most awesome goodies!

Without the luxury of space in a column like this, I cannot explain formulas in detail, so if you are stuck you will have to book a session with me. But do try to follow this “crash course”- it is easier than you think!

A typical basic Vlookup might look something like this:

=VLOOKUP(A2;Sheet1!$A$2:$B$14;2;FALSE)

• The formula will be put in cell B2 on Sheet4, and copied to the cells below
• A2 (Name and Surname) is the value that we want to match in Sheet1, within the range A2:B14.
• Note that the range should contain absolute values (hence the dollar signs) if you want to copy the formula to other cells.
• The number 2 indicates the column number within the range. Count the columns in the range and decide which column contains the data that you are looking for.
• The FALSE part simply means that we want to find an exact match.
• Note that older versions of Excel still require the use of commas instead of semi-colons!

Did you know that you can also look up data over multiple sheets?

There are of course other formula combinations that can be used, but I found this one to be the most simplistic. So here is your “cheat sheet”!

On Sheet4 we want to put a formula to look up a value in Sheet1. If the value cannot be found, Excel must look in Sheet2, and then in Sheet3.

=IFERROR(VLOOKUP(A2;Sheet1!$A$2:$B$14;2;FALSE);IFERROR(VLOOKUP(A2;Sheet2!$A$2:$B$9;2;FALSE);VLOOKUP(A2;Sheet3!$A$2:$B$6;2;FALSE)))

Note that the ranges do not have to be the same in all the sheets. I hope you enjoyed this crash course. I can be contacted on tiamari@tiamari.com

Happy clicking! Tiamari