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
Bookmarks