Super Easy Introduction to Excel's VLOOKUP and IFERROR functions.

Excel Training Reviews

FourSquare Training Article

VLOOKUP explained in under two minutes.

We're going to get you started using Microsoft Excel VLOOKUPs with a really simple VLOOKUP that anyone can learn in under two minutes.

Across the full length of the tutorial we will spend a few minutes creating a basic VLOOKUP, followed by a few more minutes extending our VLOOKUP with a basic IFERROR function, with instructions and video demonstrations. There is also a short bonus section introducing Excel's smart 'Ask and Analyze' feature at the end.

Difficulty: This tutorial has an intermediate level of difficulty and is best suited for those who are already confident using Microsoft Excel up to a beginners level and are familiar with basic math functions such as SUM, AVERAGE, COUNT, MIN and MAX.

1. INTRO: What is VLOOKUP?
   
   
2. SECTION 1: VLOOKUP Tutorial
   
   
3. SECTION 2: IFERROR Tutorial
   
   
4. SECTION 3: Ask and Analyze Tutorial
   
   

What is VLOOKUP?

Perhaps the very simplest way to conceptualise the VLOOKUP function is as a way of cross-referencing data between two different columns, and then performing actions upon that data using a set of predefined conditional statements that can be used within the function.

Said another way, Excel's VLOOKUP function essentially provides a method for searching for a specific data value in one column, based upon a data value found in a different column of your Excel spreadsheet.

VLOOKUP is a built-in function of Excel and stands for "vertical lookup" (i.e. it cross-references columns, but it is not able to cross reference rows of data).

Creating your First VLOOKUP.

We have a small farm with six egg-laying chickens. Each chicken has a name and a unique ID. We want our VLOOKUP to tell us how many eggs our chickens have laid today by looking up the chickens by their ID.

First, let's have a look at the data laid out in an Excel table.

Now let's create our VLOOKUP.

We'll start by looking at the syntax of a VLOOKUP.

=VLOOKUP(E10,C2:E7,3,0)

First, let's look at the VLOOKUP statement wrapper. This is the same for any VLOOKUP statement you write:

=VLOOKUP()

Next, there are four core parts within a simple VLOOKUP, each separated by a comma. Two variables which are cell references, one variable which is the number of columns that the lookup spans, and finally one operator at the end.

1. The first variable is the cell reference where we will input our data to lookup (E10).
   
   
2. The second variable is the cell range of our data from C2 to E7 with a colon in the middle (C2:E7).
   
   
3. The third variable is the inclusive number of columns counting from the column that we are looking up (C) to the column that we are retrieving data from (E) - so C,D,E = 1,2,3 columns.
   
   
4. The fourth part is an operator. In this instance '0' tells Excel that we are looking for an exact match (you can also use 'FALSE' to return the same result, but '0' is quicker to type). I.e. If we input anything which is not in column C then this VLOOKUP will return an error.
   
   

Tip: The data you lookup (i.e. the Chicken's ID) should be in the far-left column of the data range. Your lookup will retrieve information from a right-hand column. This is a quirk of VLOOKUP that you need to remember. Or, you can learn the new XLOOKUP feature which is multi-directional.

Data from the far-left column is what you input. Data from a right-hand column is what you retrieve.

Now that we know what a basic VLOOKUP statement consists of we are going to input our VLOOKUP in to cell E11.

Watch the video below to see our VLOOKUP come to life.

Super Simple Excel VLOOKUP (video length: 40 seconds)

As you can see in our short VLOOKUP clip, we had already entered the chicken ID #5 into cell E10, so when we finished writing our VLOOKUP function and hit enter, this returned a value of 0 eggs that were laid by chicken #5 today.

We then demonstrate the VLOOKUP function by changing the chicken ID that we are looking up to chicken #3, and we can see that chicken #3 laid 2 eggs today.

But what happens if we enter a lookup value into E10 which isn't in column C of our Excel spreadsheet?

For instance, if we enter an ID of #7 VLOOKUP returns an error because there is no chicken with that ID.

We can make this error message more user friendly by using the IFERROR function, which we're going to cover in the next section below.

If you want to learn more about Excel's VLOOKUP function, or to learn how to use the new and improved XLOOKUP function, ask us about our Excel training courses.

We really ❤ love helping organisations to master Microsoft Excel.

Our only question is: Will it be yours?

Adding an IFERROR function to a VLOOKUP function in Excel

Now that we've spent a few minutes looking at Excel's VLOOKUP function, let's spend another couple of minutes looking at the IFERROR Function.

We are going to wrap our VLOOKUP function within an IFERROR function, as follows:

=VLOOKUP(E10,C2:E7,3,0)

Now becomes:

=IFERROR(VLOOKUP(E10,C2:E7,3,0),"There is no chicken with that ID")

Now that we have one function nested within another function, we have two opening brackets and two closing brackets.

Notice that the = sign has been removed from before the VLOOKUP function. When nesting functions like this, it is important to remember to remove this = sign from the nested function or otherwise your statement won't work.

It looks complicated, but it's not too difficult when we break it down.

Our original VLOOKUP function is now preceded by the IFERROR statement and an opening bracket which nests it within the function.

=IFERROR(

Following our original VLOOKUP function is a comma which separates the VLOOKUP function from the text to display if there is an error. The error statement is inside double quotation marks and the whole thing is finished off with a closing bracket which ends the IFERROR function:

,"There is no chicken with that ID")

Now let's see Excel's IFERROR function in action.

Super Simple Excel IFERROR (video length: 34 seconds)

If you want to learn more about Excel's IFERROR function ask us about our Excel training courses.

Since we have introduced the basics of VLOOKUP and IFERROR in just a few minutes we might as well take some time to quickly introduce another handy feature of Excel. This is the smart feature 'Ask and Analyse'; or in Excel's US English 'Ask and Analyze'.

Jump to the final lesson: Ask and Analyze

We really ❤ love helping organisations to master Microsoft Excel.

Our only question is: Will it be yours?

Excel's Ask and Analyze Feature

With the 'Ask and Analyze' smart feature Excel can do a lot of work for you when it comes to presenting your data in visually insightful ways.

Right click anywhere on your table to see the popout list of available options and select 'Ask and Analyze' from the list.

The 'Ask and Analyze' smart feature will come up with a number of suggested questions to ask about the data in your table and ways to present the answer.

In this instance we chose, percentage of total 'Number of eggs laid today', which gives us a quick pivot chart visually illustrating our top and bottom performing chickens.

Next click on 'Insert Pivot Chart'

When we insert the pivot chart, Excel gives us a new page to the right of Sheet1 called Answer1. If you click on the tab for the sheet Answer1, you will see your data displayed.

This gives us rather a nice visual representation of how our chickens performed today and Excel did all the work for us just about.

If you want to find out more about Excel's 'Ask and Analyze' feature, then talk to us about booking a training course.

If you enjoyed this tutorial you might also like:

• 17 Excel Functions for Absolute Beginners
  
  
• DateDif() the Hidden Excel Function that You Should Know About
  
  
• Excel XLOOKUP Function Tutorial (with fun examples)
  
  
• The Best Places to Teach Yourself the Excel LAMBDA Function
  
  
• How do I translate Excel Functions into other Languages?
  
  
• Excel Sunburst Charts: Three Part Tutorial
  
  
• Concise Guide to Excel Shortcut Keys
  
  
• The Excel CONVERT Function Conversion Toolkit
  
  
• How Long Does it Take to Learn Excel?
  
  

We really ❤ love helping organisations to master Microsoft Excel.

Our only question is: Will it be yours?

Back to top

---

Excel Training Reviews

An excellent, clear and patient tutor who guided me through the course seamlessly.
M Menzies (Online Excel training 365 (virtual classroom))

Can I just pass on my thanks to your team for the training you provided in York. I've had lots of positive feedback from the staff involved.
J Whiley, NHS York and Selby (Excel training York)

Really useful. Really knowledgeable trainer.
A Ward, Aeromet (Advanced Excel Training Worcester)

The trainer explained everything really well. I found the course easy to follow.
V Broxham, Baywa-re Operational Services (Intermediate Excel training Milton Keynes)

Happy with everything.
M Aston, Bierce Surveying Ltd (Advanced Excel Training Aylesbury Buckinghamshire)

We have had a few people over the years but the way the trainer picked up our business and requirements so quickly – all I can is you have a diamond there.
S Jenkins, Concorde Glass (Intermediate Excel Training Grimsby)

We all thoroughly enjoyed the course, we learned a great deal and thought Sara was a brilliant trainer, very patient with a fantastic way of teaching us, so thank you for that.
C Waldock, Darlington Borough Council (Intermediate Excel training Darlington, County Durham)

The content covered the exact content required after the beginners course. The information will allow me to improve the spreadsheets currently in use.
L Powell, Dudley Building Society (Intermediate Excel Training Dudley)

Great course!
V Franklin, E-Leather (Excel Beginners Training Peterborough)

The training approach and style was great. Incredibly informative and supportive.
L Shier, One YMCA (Introduction to Excel Training Watford, Hertfordshire)

Great teacher!
J Smith, Fisher German (Intermediate Excel Training Canterbury, Kent)

Its an excellent course. I was impressed by the discovery of so many tools that will facilitate our working life. The trainer is excellent, everything was perfect.
S Green, HW Martin (Beginners Excel Training Cambridgeshire)

Very happy to learn so much.
O Chawishly, LM Information Delivery (Intermediate Excel Training Oxfordshire)

Covered all objectives. Wish We'd had two days.
B Hough, Homebase (VBA for Excel Training Milton Keynes)

The course was excellent, covering more in a single day of training than I anticipated would be possible with a high degree of clarity.
Unsigned, New Economy, (VBA for Excel Training Manchester)

Very informative, easy to follow. Trainer was clear with his explanations
S Nottingham, Denney O'Hara (Microsoft Excel training Leeds, West Yorkshire)

Excellent day!
L McLachlan, Grange School (Excel training Rochdale, Greater Manchester)

Brilliant Training, went at the right speed [and] was made relevant to work
Kathryn Strong, Tennants Distribution (Excel training Manchester)

Fantastic course, not too much information overload. Explained simple. Exactly what I needed. Many thanks.
K Stobbs, Depuy Spine - A Jonson and Jonson Company (Introductory Excel training Leeds, West Yorkshire)

The flexibility of the course was ideal.
S Walsh, Progress Rail (Advanced Excel training Edinburgh, Midlothian)

The guys who came to the course this week were very impressed with the whole set up and are now eager to attend the next level up.
E Dixon, Connaught PLC (Beginners Excel training Leeds, West Yorkshire)

The course has improved my knowledge of Microsoft Excel and was a good pace so easily understandable. Gerry was very patient and helped each of us where we needed it.
L Muir, Filtronic Broadband (Microsoft Excel training Newcastle Upon Tyne)

Although a total beginner on Excel, I found the course to be extremely helpful.
S Antonie, Filtronic (Excel training Shipley)

Made what I thought was going to be completely foreign to me easy to understand. I can actually see where I can use Excel in my day job now. Max was very good and took the time to make sure we understood. [...] Nothing could be improved.
C Elsmore, Elmwood Design (Excel training Leeds, West Yorkshire)

Very Useful.
J Collins, Cooper Research Technology Ltd (Advanced MS Excel Training Ripley, Derbyshire)

Enjoyable and informative.
N. Bell, Cosalt PLC (Excel advanced training Grimsby, Lincolnshire)

The training session went very well and I have had a lot of good feedback from the participants.
P Ogden, InterfaceFLOR (Microsoft Excel Training Halifax West Yorkshire)

Course exceeded expectations.
K. Richards, Ashfield Homes (Microsoft Excel Training Sutton-in-Ashfield, Nottinghamshire)

The day was very well constructed and presented.
D. Coulthand, Crossgates Shopping Centre, Leeds (Intermediate Microsoft Excel Training Leeds, West Yorkshire)

Training very clear. Never felt rushed or pressured to complete tasks. Each item explained and demonstrated very well. Easy to follow
S. Hope, Lear Corporation (Advanced Microsoft Excel Training Coventry, West Midlands)

Good course, good information, good trainer and at a nice tempo
A. Webster, BE Aerospace (Advanced Microsoft Excel Training Hinckley, Leicestershire)

Excellent, the best Excel course I have ever been on. Trainer gave time and patience to us all.
D. Gill, Persimmon Homes (Beginners Microsoft Excel Training Malmesbury, Wiltshire)

A very robust day's training.
T. Camp, Ideal Shopping (Beginners Microsoft Excel Training Peterborough, Cambridgeshire)

The course in Edinburgh was incredible
C. Rowley (Beginners Microsoft Excel Training Edinburgh, Midlothian)

---

Back to top

MS Excel Course Levels:

Beginners Excel

- introductory topics for working with Excel spreadsheets

Intermediate Excel

- conditional formatting, VLOOKUP, IF, apply formulas across worksheets, database features, security and conditional formatting etc.

Advanced Excel

- What-If Analysis, PivotTables, PivotCharts, Goal Seek, Sparklines, protecting workbooks, formula auditing etc.

Masterclass Excel

- 'zero to hero' three-day intensive Excel course

Excel for VBA beginners

- one or two day introduction to programming with VBA

Excel Power Query course

- Introduction to using Excel Power Query in conjunction with Pivot Tables, Power Pivot and Power Pivot Measures

Back to top

Excel Course Locations:

We have mobile trainers that can visit your company in any of the following places. These are specialist, versatile educators used to delivering flexible learning programmes in a closed course environment.

To book a training course simply call 0844 493 3699, or email info@foursquaretraining.co.uk

Back to top