Spreadsheet: 822351d1679297783-filter-function-nested-if-multi-better-simpler-expression-stephanrs_20230320
Sheet: SEARCH AZ

MAKE | MODEL | FUEL | TYPE | SEARCH < COMBO |  |  | DESIGN: HOW TO INSTRUCTIONS (EXCEL 2021 / 365)
 |  |  | Dci |  |  |  | DESC | TAB | CELL | FORMULA  / INSTRUCTIONS
 |  |  |  |  |  |  | DYN TABLE | DATA |  | SELECT DATA: INSERT > TABLE
 |  |  |  |  |  |  | SEARCHABLE | DATA | H2 | =IFERROR(INDEX(TableDiv[MAKE],MATCH(0,INDEX(COUNTIF($H$1:H1,TableDiv[MAKE]),),0)),"")
 |  |  |  |  |  |  | COL A DV | DATA | J2 | =SORT(FILTER(Table1[UNIQUE [NAME] MAKE])),"NOT FOUND"))
 |  |  |  |  |  |  | COL B DV | DATA | K2 | =UNIQUE(SORT(FILTER(TableDiv[MODEL],TableDiv[MAKE]='SEARCH AZ'!A2)))
 |  |  |  |  |  |  | COL C DV | DATA | L2 | =UNIQUE(SORT(FILTER(TableDiv[FUEL],(TableDiv[MODEL]='SEARCH AZ'!B2)*(TableDiv[MAKE]='SEARCH AZ'!A2))))
 |  |  |  |  |  |  | COL D DV | DATA | M2 | =UNIQUE(SORT(FILTER(TableDiv[TYPE],(TableDiv[FUEL]='SEARCH AZ'!C2)*(TableDiv[MODEL]='SEARCH AZ'!B2))))
 |  |  |  |  |  |  | COMBO A | SRCH | A2 | DATA > DATA VALIDATION > LIST:   =DATA!J2#                               NOTE: PART WORD SEARCH > COMBO
SEARCH RESULTS |  |  |  |  |  |  | COMBO B | SRCH | B2 | DATA > DATA VALIDATION > LIST:   =DATA!K2#
MAKE | MODEL | FUEL | TYPE | Desc | IG |  | COMBO C | SRCH | C2 | DATA > DATA VALIDATION > LIST:   =DATA!L2#
 | Focus | DIESEL | TDCi | Great | 11 |  | COMBO D | SRCH | D2 | DATA > DATA VALIDATION > LIST:   =DATA!M2#
Renault | Megane | DIESEL | Dci | Great | 8 |  | DYN 1BOX (A) | DATA | P1 | =[NAME]:[NAME]:A='SEARCH AZ'!A2)),2,1))
 |  |  |  |  |  |  | DYN 2BOX (B) | DATA | X1 | =[NAME]:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)))),3,1)))
 |  |  |  |  |  |  | DYN 3BOX (C) | DATA | AF1 | =[NAME]:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH AZ'!C2)))))),4,1)))
 |  |  |  |  |  |  | DYN 4BOX (D) | DATA | AN1 | =[NAME]:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH AZ'!C2)*(IF('SEARCH AZ'!D2<>"",(DATA!D:D='SEARCH AZ'!D2),"HELLO"))))))),5,1)))
 |  |  |  |  |  |  | DYN BLANK | DATA | AV1 | =IF('SEARCH AZ'!A2<>"","",SORT(FILTER(DATA!A:F,(DATA!A:A<>"MAKE")),1,1))
 |  |  |  |  |  |  | SRCH DYN IF | SRCH | A12 | =UNIQUE(IF(D2<>"",DATA!AN1:AS25,IF(C2<>"",DATA!AF1:AK25,IF(B2<>"",DATA!X1:AC25,IF(A2<>"",DATA!P1:U25,(DATA!AV1:BA25))))))
 |  |  |  |  |  |  | SRCH NO ZERO | SRCH | A12 | SELECT SEARCH RESULTS BELOW HDR: FORMAT CELLS > CUSTOM: 0;-0;;@
 |  |  |  |  |  |  | BLANK NO ZERO | DATA | AV:BA | SELECT BLANK CRITERIA RESULTS: FORMAT CELLS > CUSTOM: 0;-0;;@
 |  |  |  |  |  |  | 4 NESTED IF EX | EXAMPLE |  | =UNIQUE(IF(D2<>"",DATA!AN1:AS25,IF(C2<>"",DATA!AF1:AK25,IF(B2<>"",DATA!X1:AC25,IF(A2<>"",DATA!P1:U25,(DATA!AV1:BA25))))))
 |  |  |  |  |  |  | 4>3>2>1>BLANK | A12 ALT |  | =[NAME]:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH AZ'!C2)*(IF('SEARCH AZ'!D2<>"",(DATA!D:[NAME] AZ'!A2<>0,(SORT(FILTER(DATA!A:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)*(IF('SEARCH AZ'!C2<>0,(DATA!C:C='SEARCH [NAME]:F,(DATA!A:A='SEARCH AZ'!A2)*(IF('SEARCH AZ'!B2<>0,(DATA!B:B='SEARCH AZ'!B2)))),3,1))),IF($A$2<>"",IF('SEARCH AZ'!A2="","",SORT(FILTER(DATA!A:F,(DATA!A:[NAME] AZ'!A2<>"","",SORT(FILTER(DATA!A:F,(DATA!A:A<>"MAKE")),1,1)))))))