Nested XLOOKUP with MIN Function – have you used these functions nested together before?

Nested XLOOKUP with MIN Function – nesting together the XLOOKUP and MIN functions to further show their power in Excel

Nested XLOOKUP with MIN Function: Excel iconThis week’s hint and tip is on a nested XLOOKUP with MIN function in Excel. We are looking at how you can use both the XLOOKUP and MIN functions nested together in a spreadsheet. Nested functions are covered in a lot of our courses, but this exact nesting isn’t, so we decided to do a hint and tip on it. We are going to go through it now below.

 

XLOOKUP in Excel

The XLOOKUP function is covered in some of our other blogs but its always good to see a variety of examples of where this can be applied. For more XLOOKUP examples visit our website here.

We often have examples we have created or used ourselves, but instead we’re showing an example we came across recently with a customer. They wanted to see what the quote number was corresponding to the lowest quote value in a large list of quotes and values.

For this example we will use the attached file and the worksheet called ‘Quote and XLOOKUP’. You can also download the spreadsheet in the video by clicking here (clicking here will download a copy to your computer so you can try it out!).

 

Nested XLOOKUP with MIN function example

In cell E2 we have a simple MIN function looking at the lowest values in column B (we are not going to use E2 but its there as a refresher for you).

In cell D2 we have a nested XLOOKUP formula =XLOOKUP(MIN($B:$B),$B:$B,$A:$A).

If you open the functions arguments box (via the fx button) you can see the arguments part by part:

  • Lookup value – Lowest value on all quotes in column B
  • Lookup array – Find this minimum value in column B
  • Return array – Return the corresponding quote number from column A

Nested XLOOKUP with MIN function: fx screenshot image

 

 

The video below goes through how to create the nested XLOOKUP with the MIN function that you can see in the spreadsheet above. We hope that you find the video useful and enjoy learning about it!

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

We hope you liked this hint and tip on nested XLOOKUP with MIN function, why not take a look at our previous one on the Data Subtotal option with visible cells?

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