Returning data from a table based one matching one or more criteria

[CathNWN]

Can anyone help? I have a table of data which I need to extract rows from based on whether they meet one or both of two criteria.

Each row is a record and each record can have more than one value for criteria 1 or criteria 2 - I need to look at each row and see if they have either of them in any of the columns and return the ones that do.

I fear I haven't explained that very well, I've attached a sample workbook - the search I am trying to do is on the sheet 'Search' and the data I am looking at is in 'Trusts'. If anyone can see what I am trying to achieve and can think of a better way of doing it then please let me know!

[DBY]

Hello
One way would be to create a Helper column to mark the matching records. See the attached example. I hope I've understood you correctly the formulas are in the grey cells and return any names matching the Area of Interest and Project chosen.

DBY

[CathNWN]

Oh wow - such a quick response! Thanks so much! Do you know how to make it so that you can leave one of the criteria blank, giving a broader range of records returned? So for example if I select 'Elderly' but leave 'Project' blank, can we return all the records which match 'Elderly' only?

Also, would you mind explaining a little bit about what you did with the formula? Always good to learn something new!

[José Augusto]

Or try this in A12 without helper column (and copy down)

=IFERROR(INDEX(Trusts!$A$1:$A$26,IFERROR(1/AGGREGATE(14,6,1/(((Trusts!$B$2:$B$26=Search!$B$3)+(Trusts!$C$2:$C$26=Search!$B$3)+(Trusts!$D$2:$D$26=Search!$B$3)+(Trusts!$E$2:$E$26=Search!$B$3)>0)*((Trusts!$F$2:$F$26=Search!$B$7)+(Trusts!$G$2:$G$26=Search!$B$7)+(Trusts!$H$2:$H$26=Search!$B$7)+(Trusts!$I$2:$I$26=Search!$B$7)>0)*ROW($A$2:$A$26)),ROW(A1)),""),1),"")

[DBY]

Hi
Glad it was of help. You can amend the formula in the helper column with an OR argument:

=IF(AND(OR(COUNTIF(B2:E2,Search!$B$3),Search!$B$3="(Blank)"),OR(COUNTIF(F2:I2,Search!$B$7),Search!$B$7="(Blank)")),ROWS($J$2:J2),"")

I think this works ok but you will need to test it out.

Also, would you mind explaining a little bit about what you did with the formula?

Which formula in particular or both?

[CathNWN]

Thanks so much guys! I am off on holiday now for a week or so but I will check this out when I am back, thank you very much for your help!