EXCEL multiselect feature
using XLOOKUP formula

Building a function from the Excel, where there is a drop-down list, having the values (first names are stored there).

These names are stored in the Excel Worksheet, within the cells $C$6:$C$10. Corresponding date of birth is stored in the range $D$6:$D$10 on the same Excel Worksheet.

When I select one of the names from the dropdown filled by values from the range $C$6:$C$10 in the table in $C$17:$C$21, the first date of birth will be stored in the cell $F$17, and the value of last date will be in the cell $F$23, no mater, where the name of the selected value is stored at.

XLOOKUP formula is similar to VLOOKUP formula, however, compared to VLOOKUP, XLOOKUP can return the results also to the left from the array for searching, which is not supported by VLOOKUP formula.

Data Ribbon

  • Open the Data ribbon, and in the "Data Tools" section, click to the "Data Validation" button.
  • New dialog pop-ups, now heads to the "Settings" tab, and make sure, that in the "Allow" is selected "List" value.
  • Make sure, that "Ignore-Blank" and "In-Cell dropdown" are checked.
  • In the "Source" configure the area, where your data is located at.
    (e.g. the value will be like this: =$C$6:$C$10                       )
  • Next, open the "Input Message" on the same dialog, and make sure, the chceckbox "Show input message when cell is selected". On this tab are no other values required to configure.
  • Open the "Error Alert" tab dialog of this dialog, and check, if the "Show error alert after invalid data is entered".
  • Style is set to STOP, and other values will be empty.
  • Once all of these are set, hit the "OK" button and thats it. Now you will have configured dropdown list from the values configured ($C$6:$C$10)
  • If you change the value of the cell within the array used for the dropdown, it will be auto-updated without any requirements to rebuild of the list or any other changes. An example: Change of the name from "Jan" to "Janko" will allow open the dropdown and select the "Janko" value ASAP.
  • Next, select the cell, you are about to use the dropdown list use at.
  • Again, in the Data validation configure, the List validation, and choose the cells containing the data, you are willing to choose from their values.
  • This applies to every cell, where you would like to select the value from dropdown list.
  • Once finished, every cell applied, will be having the value either blank (if allowed), or by the value from the dropdown.
  • Remaining part of the trick is related to the Excel Formula.

Formulas

  • Before joining all together, make sure, that you start with formula, you will agregate later on in a single Excel Formula.
  • Based on this formula, the value Marcel value will be returned, as it is the first, non-empty value in the area $C$17:$C$21
  • The inner value for such result is:
    =XLOOKUP(TRUE;$C$17:$C$21<>"";$C$17:$C$21;;;1)
  • Based on this formula, the value Štěpán value will be returned, as it is the last, non-empty value in the area $C$17:$C$21
  • The inner value for such result is:
    =XLOOKUP(TRUE;$C$17:$C$21<>"";$C$17:$C$21;;;-1)

  • The result with these 2 formulas is nearly at the end of the formula, and this is very significant (1 or minus 1).
  • The searching area is performed in the same range $C$17:$C$21, where it accepts the cells, that have some values, though are not empty.
  • This attribute make the searching from first to last, or last to first.
  • OK, so now, we have the first and last occupied cell in the area mentioned. 
  • At this moment, we need also the Year of birth, which is located at the D column in the range $D$6:$D$10. 
  • To be able to find it, we will use once again nested function XLOOKUP as before, but this time with the result of the previous Formula, so the outcome will be like this:
    =XLOOKUP(XLOOKUP(TRUE;$C$17:$C$21<>"";$C$17:$C$21;;;1);$C$6:$C$10;$D$6:$D$10;"";0;1)
  • So if Marcel is recognized in the nested area, the external area will returned Marcel's Year of Birth: 1991

  • And just to be sure, use rather the formula IFERROR, which will make sure, that eventual errors will be resolved before calculation of formulas. 
  • The IFERROR formulas works like this: If everything is working fine, the result of the inner formula is returned. 
  • Should the error occurs, there might be stored some text warning or any other custom value.

  • Complete formula for Marcel to return his year of birth , would look like this:
    =IFERROR(XLOOKUP(XLOOKUP(TRUE;$C$17:$C$21<>"";$C$17:$C$21;;;1);$C$6:$C$10;$D$6:$D$10;"";0;1);"")
  • Complete formula for Štěpán to return his year of birth, would look like this:
    =IFERROR(XLOOKUP(XLOOKUP(TRUE;$C$17:$C$21<>"";$C$17:$C$21;;;-1);$C$6:$C$10;$D$6:$D$10;"";0;);"")
  • Please, note, that before the end of nested XLOOKUP is missing the value of 1 (from the right side of the formula, compared to the formula for Marcel).
  • Please, note, the position of any of these formulas in the Excel Worksheet are not directly related to any Worksheet cell, as the range array is specified in the formula itself using $ - Dollar symbol.