Formula to Return All Criteria Matches

Hello,

This is a different approach to find 1 to multiple matches returning all results from the AFRsDB sheet.

On my 'Multiple Search' sheet I have X's in column C which selects the criteria. I would like to return all of the results in Columns E:P which fit the criteria.

Can this be achieved with formulas please?

Thank you very much in advance for your help,
FF

Given size of results matrix and, arguably, expensive calc I would advocate either use of Table / DNR (source data), and 2 keys (stored in grouped row, column) which hold index positions of row & column.

Thank you Xlent it works great. Can you please explain the named range that you created?

first off - the prior example uses a wildcard based search which may / may not be what you want ?

the attached uses exact match which I suspect is what you want (given use of validated lists) however, you can mix/match logic by field, i.e.:

wildcard match (search term of "car" would also return "scar", "carpet" etc)

ISNUMBER(SEARCH(IF($C$3="X",$D$3,"*"),INDEX(_AFR_Data,0,13)))

whereas below logic would only return "car"

ISNUMBER(SEARCH("^"&IF($C$3="X",$D$3,"*")&"^","^"&INDEX(_AFR_Data,0,13)&"^"))


in terms of the DNR -- I defined Name thus:

Name: =_AFR_Data
RefersTo: =AFRsDB!$A$1:INDEX(AFRsDB!$AN:$AN,MATCH(REPT("Z",255),AFRsDB!$AN:$AN))

the above would create a range from A1 to ANx where x is determined by last status entry in Col AN; the calcs on search tab reference the above range.

This is very complicated and I really appreciate the way it works.

Fantastic use of formulas. Thank you kindly!