Spreadsheet: 889923d1740962205-filter-function-nested-if-multi-better-simpler-expression-combobox-filter-4multi-any-sequence-xl21-dyn-spill-srch-cam-slicer-trick-
Sheet: SEARCH AZ

MAKE | MODEL | FUEL | TYPE | SEARCH < COMBO |  |  |  |  |  | DESIGN: HOW TO INSTRUCTIONS (EXCEL 2021 / 365) FLASH FILL SEARCH RESULTS
 |  | DIESEL |  |  |  |  |  |  |  | DESC | TAB | CELL | FORMULA  / INSTRUCTIONS
 |  |  |  |  |  |  |  |  |  | DYN TABLE | DATA |  | SELECT DATA: INSERT > TABLE
 |  |  |  |  |  |  | 1234 |  |  | UNIQUE | DATA | H2 | =IFERROR(INDEX(TableDiv[MAKE],MATCH(0,INDEX(COUNTIF($H$1:H1,TableDiv[MAKE]),),0)),"")
 |  |  |  |  |  |  | 1 | 234 |  | COL A DV:  MAKE | DATA | J2 | =SORT(UNIQUE(FILTER(TableDiv[MAKE],ISNUMBER(SEARCH(_MODEL,TableDiv[MODEL])+SEARCH(_FUEL,TableDiv[FUEL])+SEARCH(_TYPE,TableDiv[TYPE])))))
 |  |  |  |  |  |  | 2 | 134 |  | COL B DV: MODEL | DATA | K2 | =SORT(UNIQUE(FILTER(TableDiv[MODEL],ISNUMBER(SEARCH(_MAKE,TableDiv[MAKE])+SEARCH(_FUEL,TableDiv[FUEL])+SEARCH(_TYPE,TableDiv[TYPE])))))
 |  |  |  |  |  |  | 3 | 124 |  | COL C DV: FUEL | DATA | L2 | =SORT(UNIQUE(FILTER(TableDiv[FUEL],ISNUMBER(SEARCH(_MAKE,TableDiv[MAKE])+SEARCH(_MODEL,TableDiv[MODEL])+SEARCH(_TYPE,TableDiv[TYPE])))))
SEARCH RESULTS
MAKE | MODEL | FUEL | TYPE | Desc | IG |  | 4 | 123 |  | COL D DV: TYPE | DATA | M2 | =SORT(UNIQUE(FILTER(TableDiv[TYPE],ISNUMBER(SEARCH(_MAKE,TableDiv[MAKE])+SEARCH(_MODEL,TableDiv[MODEL])+SEARCH(_FUEL,TableDiv[FUEL])))))
 | 2O6 | DIESEL | Hdi | [NAME] | 7
Peugeot | 2O6 | DIESEL | GT Hdi | [NAME] | 7 |  |  |  |  | COMBO A SRCH | SRCH | A2 | DATA > DATA VALIDATION > LIST:   =DATA!J2#                               NOTE: PART WORD SEARCH
Bmw | 3 series | DIESEL | Tdi | Nice | 10 |  |  |  |  | COMBO B SRCH | SRCH | B2 | DATA > DATA VALIDATION > LIST:   =DATA!K2#                               NOTE: PART WORD SEARCH
Bmw | 3 series | DIESEL | [NAME] | Nice | 10 |  |  |  |  | COMBO C SRCH | SRCH | C2 | DATA > DATA VALIDATION > LIST:   =DATA!L2#                               NOTE: PART WORD SEARCH
Peugeot | 3O6 | DIESEL | TD | [NAME] | 13 |  |  |  |  | COMBO D SRCH | SRCH | D2 | DATA > DATA VALIDATION > LIST:   =DATA!M2#                             NOTE: PART WORD SEARCH
Peugeot | 3O7 | DIESEL | Hdi | [NAME] | 11 |  |  |  |  | [NAME] | A12 |  | (IF(A2<>0,TableDiv[MAKE]=A2,SEQUENCE(ROWS(TableDiv),,1,0))* IF(B2<>0,TableDiv[MODEL]=B2,SEQUENCE(ROWS(TableDiv),,1,0))* IF(C2<>0,TableDiv[FUEL]=C2,SEQUENCE(ROWS(TableDiv),,1,0))* IF(D2<>0,TableDiv[TYPE]=D2,SEQUENCE(ROWS(TableDiv),,1,0)) )),2,1))
Peugeot | 3O7 | DIESEL | Hdi | Super | 11
Honda | Civic | DIESEL | CDTi | Great | 10
Honda | Civic | DIESEL | CDTi | Excellent | 10
Ford | Focus | DIESEL | TDCi | Great | 11 |  |  |  |  | BLANK NO ZERO | DATA | AV:BA | SELECT BLANK CRITERIA RESULTS: FORMAT CELLS > CUSTOM: 0;-0;;@
Renault | Megane | DIESEL | Dci | Great | 8 |  |  |  |  | _MAKE | SRCH | A2 | FORMULA  > NAME MNG: ='SEARCH AZ'!$A$2
 |  |  |  |  |  |  |  |  |  | _MODEL | SRCH | B2 | FORMULA  > NAME MNG: ='SEARCH AZ'!$B$2
 |  |  |  |  |  |  |  |  |  | _FUEL | SRCH | C2 | FORMULA  > NAME MNG: ='SEARCH AZ'!$C$2
 |  |  |  |  |  |  |  |  |  | _TYPE | SRCH | D2 | FORMULA  > NAME MNG: ='SEARCH AZ'!$D$2
 |  |  |  |  |  |  |  |  |  | SLICER | DATA |  | SELECT DATA (IF not already Table > INSERT > TABLE). INSERT > SLICER.  SELECT ALL > CUT > PASTE into SLICER TAB > REARRANGE.  Then Camera Trick hence 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.
 |  |  |  |  |  |  |  |  |  | SOURCE | CHANDOO
 |  |  |  |  |  |  |  |  |  |  | XL FORUM