Data Validation List based on Matched Cell Reference

Question

I am seeking help with an Match Based Array that is compatible for Data Validation List / Cell Drop Down.

Basically what I need is a Drop down list with options based on a Cell Reference Match with previous record database. If no match found (new input) default list will be shown.

Sheet 1: Input Sheet 2: Database

I used name manager and created name

strname = list of store in database {Database Sheet Cell Columnn B5:B10}

gen_addrs = for default list {Database Sheet Cell Columnn C5:C10}

m_addrs = if match list with with formula =

=INDEX(gen_addrs,MATCH(Input!$C6,strname,0),1):INDEX(gen_addrs,MATCH(Input!$C6,strname,1),1)

*relative Row Absolute Column starting with C6

In Data Validation List I tried the following formula but does not wok

=IFERROR(m_addrs,gen_addrs)

also tried but does not work

=IF(MATCH(Input!C6,strname,0),m_addrs,gen_addrs)

Looking for a viable solution.

See attached pics for more reference

Thank You!

Unless you appreciate how IFERROR() works when the alternatives are of different size (e.g. =IFERROR({"a";"b"},{"c";"d";"e";"f"}) spills across 4 cells, not 2) it is best avoided. — Spectral Instance
– Spectral Instance, Commented Jul 2 at 2:02

user26677686 · Accepted Answer · 2025-06-29 08:36:26Z

1

strname Formula:

=Database!$B$5:$B$10

2. gen_addrs Formula:

=Database!$C$5:$C$10

3. Data > Data Validation > Allow: List In the Source, paste this formula:

=IFERROR(FILTER(gen_addrs, strname = C6), gen_addrs)

answered Jun 29 at 8:36

user26677686

Sign up to request clarification or add additional context in comments.

1 Comment

Community Jun 29 at 8:42

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Spectral Instance · Accepted Answer · 2025-07-02 01:59:38Z

0

With this formula

=LET(a,MATCH(Input!C6,strname,0),IF(ISNA(a),gen_addrs,INDEX(gen_addrs,a):INDEX(gen_addrs,COUNTIF(strname,Input!C6)+a-1)))

(the slightly shorter

=LET(r,MATCH(Input!C6,strname,0),IF(ISNA(r),gen_addrs,TAKE(DROP(gen_addrs,r-1),COUNTIF(strname,Input!C6))))

could be used instead)

used in your data-validation rule:

the data-validation can be seen to work:

edited Jul 2 at 1:59

answered Jun 28 at 21:50

Spectral Instance

2,7792 gold badges5 silver badges13 bronze badges

5 Comments

JeremyLongs Jul 2 at 9:13

Thank you for pointing out that I need to understand the real purpose of IFERROR and for providing a working solution using a LET formula. Your answer is straightforward and worked on the spot. I opted for the 2nd answer since I am working with legacy excel and LET formula will not work with some old excel version. Thank you for your time and Effort appreciate it :)

Spectral Instance Jul 2 at 11:49

The LET() function can be rewritten for legacy Excel

=IF(ISNA(MATCH(Input!C6,strname,0)),gen_addrs,INDEX(gen_addrs,MATCH(Input!C6,strname,0)):INDEX(gen_addrs,COUNTIF(strname,Input!C6)+MATCH(Input!C6,strname,0)-1))

- while this is longer, it completely avoids any volatile functions (INDIRECT()/CELL()/OFFSET()) which may negatively impact performance

JeremyLongs Jul 3 at 13:05

Subarashii! Thank you for giving an alternative legacy compatible and non volatile data solution! Much more usable and I use this now in my workbook. Apologies that I cannot give you the answer credit anymore. Godbless you sir!

Spectral Instance Jul 3 at 15:55

Arrigato for expanding my miniscule Japanese vocabulary! Because I take issue with "Here therefore the INDEX/MATCH structure cannot be applied, because Excel not accept the : range operator." (my answer disproving both assertions) you can change your accepted answer.

JeremyLongs Jul 4 at 5:27

thank you also for the new knowledge that accepted answers can still be modified. I have change it to your answer; hopefully this thread will also help other solution seekers with the similar problem as mine.

Collectives™ on Stack Overflow

Data Validation List based on Matched Cell Reference

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related