Excel Data Validation with XLOOKUP

Expected result 1: Data validation in D6 with list of unique values from column A - this works

Expected result 2: Data validation in D17 with list of values from column B where column A = D6 - this does not work - only the first value 'red' is shown in D17 data validation drop down - how to change for it to show both red and green?

=XLOOKUP($D6,INDIRECT("TableTest[A]"),INDIRECT("TableTest[B]")) 

XLOOKUP() is doing nothing wrong, it is behaving exactly as it needs to be, it will always return the first occurrence of a duplicate lookup value. That said, there are two methods of accomplishing the desired output,

• First Method : Using OFFSET()+XMATCH()+COUNTIF() with INDIRECT() embedded within the functions.

---

• Formula used in Data Validation for [B]

=OFFSET(INDIRECT("TableTest[B]"),
  XMATCH(D6,INDIRECT("TableTest[A]"))-1,,
  COUNTIF(INDIRECT("TableTest[A]"),D6))

---

In the above formula we can avoid using INDIRECT() as well, if we don't consider the Structured References thus the formula will be:

=OFFSET($B:$B,XMATCH(D$6,$A:$A)-1,,COUNTIF($A:$A,D$6))

---

• Second Method : Using helper columns with the FILTER() function and using cell reference with # Spill operator in Data Validation

---

• Formula used in cell A14 (This can be placed anywhere since its a helper column preferably somewhere hidden)

=FILTER(TableTest[B],TableTest[A]=D6,"")

---

• Formula used in Data Validation

=A14#

---

_{NOTE: Some caveats here, the first method will always work when the data is sorted by [A], secondly it is highly suggested to avoid using volatile functions like INDIRECT() and OFFSET() since these functions will make Excel work relatively slow, as well as it will keep recalculating whenever there is a change in any open workbook, finally these functions are single threaded. Regarding the second method, one can use it with the helper columns and there shouldn't be any issues arising for the same.}

---