VLOOKUP, HLOOKUP, XLOOKUP, LOOKUP

VLOOKUP, HLOOKUP, XLOOKUP, LOOKUP. What do you use these functions for? Would you use one over the other? Can you combine them to create a super function? Is it possible to nest one function inside another and more importantly when should you do so?

Some people prefer in person training for their learning experience. If that is so then you can do better than check out this Intermediate Excel 365 training course in London or online. With a 5 star review on trust pilot it’s the fastest and most effective way to bring your Excel skills up to scratch.

Now back to the the reason you are here. VLOOKUP vs HLOOKUP vs LOOKUP vs XLOOPUP. Let’s start at XLOOKUP.

We are going to look at XLOOKUP and see why it is superior to VLOOKUP, HLOOKUP and LOOKUP. We will use XLOOKUP in 3 practical ways. First, cross-referecing an ID column. Second, matching an item in a range of values. Finally, we will see how to use wildcards in XLOOKUP.

As a result, you will understand that in the xlookup vs vlookup vs hlookup vs lookup battle there is no contest. XLOOKUP does the job of the other functions and more.

Download Exercise File – If you want to follow along.

XLOOKUP Basics

Let’s begin with the basics of XLOOKUP. The XLOOKUP function is the new kid on the block. Consequently, it addresses a lot of the issues that VLOOKUP and HLOOKUP had. Due to this, if you can get XLOOKUP under your belt you never have to learn another lookup function.

Using XLOOKUP vs VLOOKUP

Before you start to use XLOOKUP you need to understand that you can use XLOOKUP in much the same way as you would VLOOKUP. So if you have two worksheets that contain data that you want to cross-reference.

XLOOKUP vs VLOOKUP with 2 ranges of data

This is usually a VLOOKUP stable. But how would you use XLOOKUP in the above situation. Use the xlookup-function exercise file to follow along, ensure that you are on the Standard XLOOKUP tab.

  1. First, click where you would want the answer displayed. In this instance it would be cell C2.
  2. Now click on the “Insert Function” button to the left of the formula bar. (We might as well use the guide to help).
  3. In the Insert Function box search for XLOOKUP.
  4. In the XLOOKUP function arguments box click in the Lookup_value box.
  5. Now click on Cell B2. This contains the customer ID you will use as a cross reference.
  6. Next click in the Lookup_array box.
  7. Then select the Customer ID cells in the Customers Table. This would be cells F2 – F5, after press the F4 shortcut on the keyboard to add the dollar signs.
  8. Now click in the Return_array box and select cells G2 – G5, again press F4 to add the dollar signs.
  9. Check the picture below to see if your XLOOKUP function arguments box looks the same and then click OK.
XLOOKUP Function Arguments box. Similar to VLOOKUP and HLOOKUP but with more

If all is well, the XLOOKUP function arguments box should look like the above picture.

Now you can AutoFill down.

AutoFill XLOOKUP just like VLOOKUP

What about /#N/A errors?

#N/A errors (XLOOKUP, VLOOKUP, HLOOKUP,LOOKUP)

If a customer ID doesn’t exist in the Customers Table then the XLOOKUP function will display #N/A meaning “Not Applicable”. XLOOKUP allows you to create your own “Custom” message to handle this problem.

  1. Click cell C2 then press the Insert Function button.
  2. In the If_not_found box type “Customer Not Found” then click OK.
XLOOKUP Function Arguments - If_not_found

Note: Excel will automatically add the quotes.

Now whenever the XLOOKUP function can’t find a matching Customer ID the text “Customer Not Found” appears. Now, doesn’t that make a better error message than #N/A?

Hopefully you can already see that in the battle between VLOOKUP, HLOOKUP, XLOOKUP and LOOKUP. XLOOKUP is starting to show that it’s something you should consider using.

XLOOKUP with a Range of Values

Next, we will look at what you can do to get XLOOKUP to work with a range of values. On the XLOOKUP Range sheet you’ll find two ranges that are coloured yellow and grey.

XLOOKUP with Range

First looking at this it seems that a VLOOKUP could handle this quite easily. However, if you are looking to return an approximate match from a VLOOKUP the lookup table, the range in grey, has to be arranged in ascending order.

As you can see from above the lookup array, column G, is arranged from the highest spend down to the lowest. How can you use XLOOKUP to return a values from a range?

  1. Click in Cell D2.
  2. Click Insert Function.
  3. Double click on XLOOKUP or if it’s not there search for it. (The function should be in the Select a function list as you’ve just used it above).
  4. Enter the following in the appropriate argument boxes:
    • Lookup_value – C2
    • Lookup_array – $G$2:$G$6
    • Return_array – $H$2:$H$6
    • If_not_found – “A+”
    • Match_mode – 1
  5. The XLOOKUP Functions Arguments box should look like:
XLOOKUP Function arguments box.

There are a couple of things to note. First, we’ve added “A+” into the If_not_found box to take care of any customers whose spend is above £9,000. Secondly, we’ve enter a number 1 into the Match_mode box. Why have we entered 1 into the Match mode box.

What’s the Match Mode argument in XLOOKUP

The last argument has 4 options available:

  • 0 – This is the default mode. The choice that will automatically be made should we not enter in a number. That is why for the first exercise we didn’t type anything into this box.
  • -1 – Negative or minus 1 is what we would type should we want to return a number that is less that the reference value. So in the above example this would not work as the spend is arranged in “Descending Order”. This means the highest spend is at the top of the column in cell G2. While the lowest spend is at the bottom in cell G6.
  • 1 – Positive one is if the Lookup_array is sorted in Descending order, and this is the one we have used for this example. The only downside to this is that if someone spends over £9,000 then we will get an error. However, we can cover this by using the If_not_found argument to return the “A+”.
  • 2 – Entering a 2 means accepting a wildcard in the value. We will look at that later. So make sure you return so you can see how.
XLOOKUP with range AutoFilled

Now you know how XLOOKUP works you can use this in place of VLOOKUP. One word of warning though is, presently, XLOOKUP only works with Office 365.

XLOOKUP vs VLOOKUP Wildcards

XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP Comparison

Hopefully you are already appreciated the benefits of using XLOOKUP over VLOOKUP, HLOOKUP, LOOKUP and maybe even INDEX MATCH. If, however, you are still living the the realms of the skeptical then let’s see if I can convince you.

You can use XLOOKUP with wildcards should you want to return a list of values based on text that you type in another cell. Have a look at the following picture.

XLOOKUP using Wildcards

As you can see from above I have typed “fred” in cell F2. XLOOKUP, using the wildcard has looked at the list and returned the full name and the spend for Fred Bloggs.

Also, I have taken the liberty of encasing the XLOOKUP inside an IF function.

How to Use XLOOKUP with Wildcards

First thing we need to do is to click on the XLOOKUP Wildcards sheet should you want to follow along.

  1. Starting of, click in Cell G2 and click Insert Function find XLOOKUP and click OK.
  2. Next, in the Function Arguments box, enter the following:
    • Lookup_value – F2 & “*”
    • Lookup_array – B2:B16
    • Return_array – B2:B16
    • Match_mode – 2
    • Search_mode – 1
  3. Click OK.

As you can see, there’s no need to type anything in the If_not_found box. Also, note that in the Lookup Value I’ve entered F2 & “*”. You can use the asterisk to represent any character. So, in essence, what I’m telling the XLOOKUP function to do is to search for any Customer Name that begins with whatever I’ve type in cell F2. Finally, the Search_mode is set to 1. This means that if the XLOOKUP finds multiple items with the same value in the list it will find the first item it comes to in ascending order, or top down. Should I want bottom up then I would type -1.

Now, we need to enter the formula for the Spend.

  1. Click in Cell H2 and click Insert Formula and enter the following:
    • Lookup_value – F2 & “*”
    • Lookup_array – B2:B16
    • Return_array – C2:C16
    • Match_mode – 2
    • Search_mode – 1
  2. Click OK.

Note that I haven’t made use of the dollar signs $ to fix the references. There is no need for me to do this as I’m not AutoFilling the formulas down.

Finally, I can encase both formulas in an IF function. So the completed formulas, with the new bits in red and bold, for cell G2 is:

=IF(F2<>””,XLOOKUP(F2 & “*”,B2:B16,B2:B16,,2,1),””)

The formula for cell H2 is:

=IF(F2<>””,XLOOKUP(F2&”*”,B2:B16,C2:C16,,2),””)

Enhance your Excel skills with our engaging and interactive “Excel 365 Beginners Course“. Ideal for those new to Excel or seeking a comprehensive refresher, this course provides three hours of live, online instruction with an experienced trainer. Delve into the fundamentals of Excel 365, from basic formulas to data management, all from the comfort of your home or office. Whether you’re looking to boost your productivity at work, manage personal projects more efficiently, or simply expand your software knowledge, this course is tailored to suit your learning pace. Click here to explore the course details and embark on your journey to mastering Excel 365.