Tracy van der Schyff

#Office365Challenge VLOOKUPS are used to retrieve data from other tables or ranges by row. A common field is needed to match and return stated values.

In my example I have 3 different tabs in Excel. Once where I will populate an invoice, one where I will find unit of measure and price and another where the stock is found.

In the Invoice section – I would like to “lookup” the Unit of measure, Rate and Stock from the other sheets:

Here you’ll see the Unit of Measure and Rate:

And here we’ll find the available stock:

• Format the cells in the Invoice areas to correctly display the data you will bring back
• The text it must match – must always be in the leftmost column of the table – to retrieve data to the right of it.

How to:

When you start to type ‘VLOOKUP’ Excel will supply a definition of the formula:

Take note of the use of “,” or “;” in the formula.

You need to select the field to find (A3):

Enter a “;” or comma after the A3 cell, now navigate to sheet 2 – select the whole table:

Enter a “;” or comma after the table selection, add a “2” to indicate which column to return after it found the name in the product column, add “;” or comma then false at the end (for when no values are found):

The unit will now be returned. I’ve fixed the table (A3:C7) by adding “$” before and after and can now copy and paste this formula to all the Units of Measure and Rate cells:

Remember to change the column reference under Rate from “2” to “3”. Now do the same for Stock Available:

I would suggest getting all the product names with Data validation – list. This will eliminate typos and formulas then not working. To see how to do this, read here…

Join me tomorrow when we’ll take a look at VLOOKUP formulas.

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.

Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.