What's Up East Coast Column

Read all about it!

You got the lowdown in the column, now get the breakdown. (A very special THANK YOU to Diane Pullen from WUEC for making this column possible!)

ADVERTISE in the popular WUEC magazine and post your events for free! This full colour booklet has been around for over 5 years and several thousands of hard copies are printed and distributed bi-monthly in the Eastern Cape area.
Contact me TODAY to get your listing up. It costs less than you think!

        28 May 2015, 1:08 pm

        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:$B;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:$B;2;FALSE);IFERROR(VLOOKUP(A2;Sheet2!$A:$B;2;FALSE);VLOOKUP(A2;Sheet3!$A:$B;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

            Showing all records: