0

I am trying to come up with a formula to search a data set and need it to return ALL ROWS of matching data. The search criteria is text and needs the ability to search partial words and not just an exact word match. I do not have the "FILTER" function in my version of excel.

Example: If im searching for the color "green", I need to be able to input any part of the word "green" ("gr" or "re") under the search cell and have the formula return ALL ROWS of data containing the search term, even if it is not an exact match (example: search term of "gr" or "re" would return all rows of data containing the word "green").

Data Range

enter image description here

My current formula:

=INDEX(Sheet1!$A$8:$G$999, SMALL(IF($B$3=Sheet1!$C$8:$C$999, ROW(Sheet1!$C$8:$C$999)-MIN(ROW(Sheet1!$C$8:$C$999))+1, ""), ROW(Sheet1!A1)),COLUMN(Sheet1!A1))

formula output pic

enter image description here

This above formula only returns an exact match of "Br Aug" and does not allow for partial searching "Br". Many other procedures within the data set contain "br aug" but will not populate as a search result because "Br Aug, Lipo" is not an exact match of "Br Aug", even though "Br Aug" is part of the cell. As mentioned earlier, I need the ability to search for partial letters within a word ("br") instead of an exact word match ("br aug"), and I need the formula to return the entire row of data containing the search value, even if it is only a partial match ("br aug" search term would be able to return all rows from the data set for cells containing both "br aug" and "Lipo 360, br aug, lift").

Furthermore, I have to copy this formula down to however many rows I want it to display. If I want 10 rows, I need to copy the formula down 10 rows. I need this formula to display all rows of matching criteria, and have the rest of the spreadsheet rows blanks instead of some sort of error "#NUM" or "#VALUE".

Please help, I'm at a loss here and cannot get this to work for me. Thank you.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Clint Gebert
  • 19
  • 1
  • This has to be an excel formula -- the included Filter function for data sets will not work for what I'm using this for. I need to be able to input search criteria and have it spit out all matching rows of whatever I input. – Clint Gebert Jun 30 '23 at 17:30
  • 1
    Please post your data as text that can be copy/pasted. See [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question) – Ron Rosenfeld Jun 30 '23 at 18:24
  • See https://stackoverflow.com/a/72935318/4961700 – Solar Mike Jun 30 '23 at 18:48
  • Try: `=IFERROR(INDEX(Sheet1!$A$8:$G$999,AGGREGATE(15,7,(ROW(Sheet1!$A$8:$A$999)-ROW(Sheet1!$A$8)+1)/(ISNUMBER(SEARCH($J$1,Sheet1!$C$8:$C$1999))),ROW(A1)),0),"")` – Mayukh Bhattacharya Jun 30 '23 at 18:52
  • 1
    Mayukh Bhattacharya -- this is on the right track. It works for partial searching, but it only results data from column A, which is the date. I need it to result the entire row of data -- columns A-G. Any idea how to do this? – Clint Gebert Jun 30 '23 at 19:37
  • Hi I just posted in answers please refer you need to fill down and fill right. It can be made more dynamic if the headers on the output sheet is same. Using a match function. I will update it once my computer is restarted – Mayukh Bhattacharya Jun 30 '23 at 20:02

1 Answers1

2

You could try using INDEX() & AGGREGATE() function :

enter image description here

• Formula used in cell A7

=IFERROR(INDEX(Sheet1!A$8:A$999,
AGGREGATE(15,7,(ROW(Sheet1!$C$8:$C$999)-ROW(Sheet1!$C$8)+1)/
(ISNUMBER(SEARCH($B$3,Sheet1!$C$8:$C$999))),ROW($Z1))),"")

Formula needs to be filled down and filled right. Avoided to use INDEX() and SMALL() which needs to hit CTRL+SHIFT+ENTER while exiting the edit mode.

Using search key as br would return all those words having br note that, as shown in the screenshot.

Update:

enter image description here

• Formula used in cell A7

=IFERROR(INDEX(Sheet1!$A$8:$G$999,
AGGREGATE(15,7,(ROW(Sheet1!$C$8:$C$999)-ROW(Sheet1!$C$8)+1)/
(ISNUMBER(SEARCH($B$3,Sheet1!$C$8:$C$999))),ROW($Z1)),
MATCH(A$6,Sheet1!$A$7:$G$7,0)),"")
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
  • 1
    This works perfectly! Much appreciated – Clint Gebert Jun 30 '23 at 20:07
  • @ClintGebert just updated the formula. Now you can alter the headers and use the formula it will be dynamic. Also since it has worked do accept the answer by ticking the checkmark beside so that it would help others in future looking for a same solution. – Mayukh Bhattacharya Jun 30 '23 at 20:13