Nested XLOOKUP Function in Excel – taking the XLOOKUP function a step further!

Nested XLOOKUP Function in Excel – an example of a nested XLOOKUP, can you follow it?

Nested XLOOKUP in Excel: Excel iconThis week’s hint and tip is about an example of a nested XLOOKUP function in Excel. We are looking at how you can take a simple XLOOKUP a step further through nesting it inside another. This isn’t specifically covered on our Excel course, so we decided to do a hint and tip on it. We are going to go through it now below.

 

Nested XLOOKUP Function

To explain a nested XLOOKUP please look at the enclosed workbook ‘Nested XLOOKUP Function in Excel’ (clicking here will download a copy to your computer so you can try it out!).

An XLOOKUP can have up to 5 arguments within it and in this example, we are using an external XLOOKUP with 5 arguments and an internal XLOOKUP with just 4.

The description of each part is stored in a text box on the worksheet.

 

XLOOKUP Function with 5 arguments

The functions wizard box shows the 5 arguments very well below.

Nested XLOOKUP function in Excel: screenshot of XLOOKUP with 5 arguments

This part of the formula is all to do with the ages effectively looking into column A to see the lower age range entry so a 56 year old takes the 55 year old entry row and a 24 year old would take the 20 year old entry row. (-1 means take the lower entry in the range).

If we had a 12 year running the ‘If not found’ message appears as there is no lower entry below 20 years.

 

XLOOKUP Function with 4 arguments

The time range is gained from the internal XLOOKUP, again the function arguments wizard shows this well.

Nested XLOOKUP function in Excel: screenshot of XLOOKUP with 4 arguments

Effectively we are looking for the time held in B20 into a lookup range on row 3 and then the corresponding return entry is taken from the entire range but as that range has been limited to column I from the external XLOOKUP it is simply the intersecting entry between Column I and row 11.

Have a try of some ages and times and then see what grading a person gets.

It is not always necessary to use XLOOKUP but it has effectively replaced the more traditional functions below:

  • VLOOKUP
  • HLOOKUP
  • INDEX
  • MATCH

 

The video below goes through the nested XLOOKUP that you can see in the spreadsheet above. It talks you through how the example was created and how the nested XLOOKUP was put together. We hope that you find the video useful and enjoy learning about it!

(VIDEO CORRECTION: in the textbox in the video, the age should be 56 and not 58)

Take a look below at the video to find out more!

For more blogs and videos please click here.

 

We hope you have enjoyed this hint and tip on nested XLOOKUP function in Excel. Why not take a look at our previous one on the scheduling assistant in Outlook?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy