# Using Dynamic Lists with duplicating criteria

1. ## Using Dynamic Lists with duplicating criteria

I'm not sure if I'm explaining this correctly but I've included the attachment below. Basically, I have three rows of data:

Month - Jan & Feb
Shop - North, South, West
Type - Fruit or Vegetable

The issue is I want the ability to select 0, 1 or 2 shop criteria. The formula is working for 0 or 1, but for 2, I'm running in issues. For example, if I select South first, then West, nothing happens. I've attached my formula below that I've been using, and attached the spreadsheet to the message. I'm assuming I need to use OR/AND but had issues putting those in. Any ideas? I'm looking for a non-VBA solution. Thanks!!

=IFERROR(INDEX(D:D,SMALL(IF((IF(\$G\$6<>"",\$A\$2:\$A\$25=\$G\$6,TRUE))+(IF(\$G\$9<>"",\$B\$2:\$B\$25=\$G\$9,TRUE))+(IF(\$G\$12<>"",\$B\$2:\$B\$25=\$G\$12,TRUE))+(IF(\$G\$3<>"",\$C\$2:\$C\$25=\$G\$3,TRUE))=4,ROW(\$C\$2:\$C\$25)),ROW(C1))),"")

New Model_Test.xlsx

2. ## Re: Retriving best score and week from a table to another table

Hi Alex,

I would avoid the arrays and use an added KEY column to individually mark the rows that match your search criteria, then INDEX/MATCH to draw those key rows over to your output table. Like so:

3. ## Re: Retriving best score and week from a table to another table

Thanks for your help! The formula is a lot easier to manage. However, when I select North and West, I get no data. My desired outcome would be produce all the lines that have North or West in them.

4. ## Re: Using Dynamic Lists with duplicating criteria

My bad, let's change the SHOP test.... use this:

=IF(AND(\$J\$3<>"", \$J\$3<>C2), N(G1), IF(AND(\$J\$6<>"", \$J\$6<>A2), N(G1), IF(AND(\$J\$9&\$J\$12<>"",NOT(ISNUMBER(SEARCH(B2, \$J\$9&\$J\$12)))), N(G1), N(G1)+1)))

5. ## Re: Using Dynamic Lists with duplicating criteria

Thanks! This did the trick.

6. ## Re: Using Dynamic Lists with duplicating criteria

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1