(Last Updated On: 16/06/2020)

Complex Functions

Create A VLOOKUP Function

Great, you have been told you need to do a VLOOKUP. What is a VLOOKUP? When should I use it?

A VLOOKUP function in Microsoft Excel is a glorified copy and paste tool. It looks for a matching piece of information finds it in a table and brings back information it found from a specific column.

Imagine you have a massive set of data it goes on for ever, but you are only interested in a handful of columns from this data set. Use a VLOOKUP to make a smaller refined set of data and the data is still linked to the original so as updates occur you still have the latest information.

You could use a VLOOKUP for the purpose of making a smaller list and then using that list to create a PivotTable.

If you have an Invoice No, Order No, Product Id use the VLOOKUP function to bring back the description.

Lookup Functions

There are many types of lookup functions in Microsoft Excel, there is LOOKUP, VLOOKUP, HLOOKUP, CHOOSE, MATCH and INDEX. The VLOOKUP is the simplest and therefore the most popular. The V in VLOOKUP stands for Vertical lookup. The H in HLOOKUP stands for horizontal lookup.

Most times if your lookup value is presented horizontally you may have decided to select the data and perform a Copy/Paste Special Transpose then go ahead and do your VLOOKUP Function. If you only knew, you could have simply used the HLOOKUP function in Microsoft Excel.

When creating a VLOOKUP remember it will only return data from the right. If you need it to return data from the Left of the LookupValue use the INDEX function with the MATCH function together.

There are a few things to check before you can go crazy and start typing your VLOOKUP function. The table must be SORTED by the FIRST column in your Master List or table. Make sure you know the column number before you start typing your function.

The structure for the VLOOKUP is as follows.

=VLOOKUP(Lookupvalue,Table,Col Number,True or False)

You can replace the table data with a Range Name, this will help to understand what the VLOOKUP is doing.

False or 0 means: Exact match to Lookup value

True or 1 Means: Nearest Lowest match to Lookup value (True is the default)

If you need to find the nearest highest, then the VLOOKUP is not the right tool for you. Try a different function like INDEX and MATCH together.

VLOOKUP Function Errors

The Location of the LookupValue is important. It should be positioned in the first column of the start of your table. When cross matching data and extracting information from a specific column use VLOOKUP.

However, there are times when a VLOOKUP won’t do the job because it can only look to the right for the data to extract. If the lookup value in the Master List (Table) is located somewhere in the middle and you find yourself cutting and pasting the lookup value to the first column of the table.

That’s when you will need to use INDEX and MATCH function instead.

Take care of the lookup value and how it is formatted. If your lookup value is formatted for number and in the other area it is formatted as text or there is an extra space at the end. then the VLOOKUP will not work. To get rid of the extra space try the TRIM function.

What you can do to make your VLOOKUP function in Microsoft Excel so much easier to read is to use Range Names. Instead of selecting the Sheet and then the Master Data use a Range Name. You can also use Range Names in Data Validation Lists.

Scenario:

You have been asked to Match these invoice numbers to the master list and return the information in the amount columns.

vlookup
master list

Drop Down Lists

To avoid entering incorrect product codes, account names, surnames…

To limit options to a pre-populated list try creating a Data Validation List using Excel. In a separate sheet create a list of the data to display in the drop down, for example a list of department names or product descriptions.

You might like to then hide the sheet by right mouse clicking on the sheet name and selecting hide.

Data Validation

Create List

  1. Type a list of States
  2. Select the cell/s where you want the drop-down list to display
  3. Go to the Data Tab, Data tools group
  4. Select Data Validation
  5. In the Allow field
  6. Choose List form the drop-down arrow
  7. In the Source Field
  8. Select the range of cells with the States
  9. If you have given the States a Range name, then Press = and type the range name or Press F3 and select from the list
  10. You know have a drop-down arrow to select a state for your data entry.

YouTube Create A Data Validation List

XLOOKUP Supersedes VLOOKUP Function

Great, you have been told you need to do a VLOOKUP. What is a VLOOKUP? When should I use it?

A VLOOKUP function in Microsoft Excel is a glorified copy and paste tool. It looks for a matching piece of information finds it in a table and brings back information it found from a specific column.

Imagine you have a massive set of data it goes on for ever, but you are only interested in a handful of columns from this data set. Use a VLOOKUP to make a smaller refined set of data and the data is still linked to the original so as updates occur you still have the latest information.

You could use a VLOOKUP for the purpose of making a smaller list and then using that list to create a PivotTable.

If you have an Invoice No, Order No, Product Id use the VLOOKUP function to bring back the description.

Well that’s all great but now there is a much better function called XLOOKUP.

Structure for VLOOKUP IS

=VLOOKUP(LookupValue, table array, column number, Match)

What makes XLOOKUP so great?

I got excited when I learned about XLOOKUP Function it is better than VLOOKUP or INDEX and MATCH Function put together. XLOOKUP is an improvement of the VLOOKUP functions inabilities.

I have included an Excel file so you can follow along and further below you will find a link to a YouTube example.

For example, with the VLOOKUP you can only find an exact match or nearest lowest match. In the past you would have had to add the MATCH function to be able to do nearest highest match.

VLOOKUP returns #N/A when it can’t find a match.  You had to add the IFERROR function to return a comment instead, for example Not in stock. XLOOKUP function has this built in. So far you are saving nesting two other functions, the IFERROR and MATCH.

If you find yourself needing to create for example 3 VLOOKUP Functions where you need to return information from column 2, 3 and 4. First Name, Surname and Phone number. That’s 3 VLOOKUP Functions not anymore. XLOOKUP will prefill cells to the right by spilling the function.

What do I mean by that, watch the YouTube video for clarification?

So, exited on top of that it protects the cells when you use the spill feature.

Structure for XLOOKUP IS:

=XLOOKUP(LookupValue, lookup array, return Array, if not found, match mode, search mode)

(If you have used a VLOOKUP before the third item was “COLUMN NUMBER”. This is now an “ARRAY”.)

Example

=XLOOKUP(A9,Stock_List!D$9:D$28,Stock_List!E$9:G$28,”check code”,0)

For better readability of a function use Range names.

Example

=XLOOKUP(A9,ProdNo,Title_Cost_Code,”check code”,0)

If not found is your IFERROR function built in. This is where you can enter

  • Comment
  • Value
  • Formula
  • Function for example “discontinued” to display text.

XLOOKUP Match mode is either a:

0 for exact – you can still use the word False if you prefer

1 for exact or nearest lowest – you can sill use the word True if you prefer

-1 for exact or nearest highest

2 Wildcard character match (* ? ~)

XLOOKUP Search Mode

The * is used to represent any number of characters – for example * pty Ltd

The ? is used to represent one character – for example A?6 or A???6 each ? is one character in that position.

The ~ represents a literal character – for example *~? Returns Hello?

XLOOKUP Function YouTube video coming soon 4th May 2020 watch FREE 30 Min Webinar

Conclusions

Most popular function for cross checking data, looking up data from a master list, using it to create a smaller list for a Pivot Table is the VLOOKUP Function. Also try HLOOKUP or INDEX and MATCH Functions in Excel.

The XLOOKUP Function is the latest addition to Microsoft Excel, benefits include not limited by having the lookup value in the first column.

Can find a match which is nearest highest and find based on wildcard characters (*).

The XLOOKUP Function can find the last lookup value in a list instead of only the first. You now get a choice. Last or First occurrence to return.

Cell protection without having to set up cell protection.

It has an IFERROR component built in.

Microsoft Office Small Group Training Sessions

AZ Solutions delivers customized training courses in Sydney – Australia. We come to you. All you need is a board room, PC’s for each student and a TV/ Projector with a HDMI connection cable.

In our training sessions you are welcomed to bring examples of your work to class. We prefer it.

Call Now M 0414 417 059 visit www.azsolutions.com.au

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 30 other subscribers