Nested XLOOKUP Function in Excel – an example of a nested XLOOKUP, can you follow it?
This 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. The XLOOKUP function is covered in our Master Class Excel Silver training course, but this example isn’t specifically covered on the 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.
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.
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?