Spreadsheet: 889926d1740962521-filter-function-nested-if-multi-better-simpler-expression-combobox-filter-4multidependent-unique-az-srch-xl21-dynamic-spill-srch-cam-slicer-trick
Sheet: SEARCH AZ

MAKE | MODEL | FUEL | TYPE | SEARCH < COMBO |  |  | DESIGN: HOW TO INSTRUCTIONS (EXCEL 2021 / 365)
Peugeot | 2O6 |  |  |  |  |  | DESC | TAB | CELL | FORMULA  / INSTRUCTIONS
 |  |  |  |  |  |  | DYN TABLE | DATA |  | SELECT DATA: INSERT > TABLE
 |  |  |  |  |  |  | SEARCH MAKE | DATA | H2 | =IFERROR(INDEX(TableDiv[MAKE],MATCH(0,INDEX(COUNTIF($H$1:H1,TableDiv[MAKE]),),0)),"")
 |  |  |  |  |  |  | COL A DV | DATA | J2 | =[NAME] 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 > ENTER WORD > RETURN
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#
 | 2O6 | DIESEL | Hdi | [NAME] | 7 |  | COMBO D | SRCH | D2 | DATA > DATA VALIDATION > LIST:   =DATA!M2#
Peugeot | 2O6 | DIESEL | GT Hdi | [NAME] | 7 |  | DYN 1BOX (A) | DATA | P1 | =[NAME]:[NAME]:A='SEARCH AZ'!A2)),2,1))
Peugeot | 2O6 | PETROL | GTI | Sporty | 10 |  | 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)))
0 | 0 | 0 | 0 | 0 | 0 |  | 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))))))
 |  |  |  |  |  |  | SLICER | DATA |  | SELECT DATA (IF not already Table > INSERT > TABLE). INSERT > SLICER.  SELECT ALL > CUT > PASTE into SLICER TAB > REARRANGE.  Then Cam same SOURCE DATA.
 |  |  |  |  |  |  | CAMERA IMG | DATA |  | DATA TAB > SELECT DATA TABLE > CAMERA > SLICER TAB > CELL.  Note: To add in Toolbar (Whilst not in Cell) > r/c Top Bar Customize Quick Access Toolbar >
 |  |  |  |  |  |  |  |  |  | Choose from > ALL COMMANDS > Scroll to CAMERA > ADD.