Return all wildcard matches in a range

[kateie]

Hi,

I have a spreadsheet over multiple rows and columns (A1:AZ102) with cells containing text.

I want excel to find all instances of a specific word in this range and return them to me in a list. I need to use a wildcard function as the word will only be part of the cell contents.

I can currently achieve this in a single column with this formula:

=IF(E3>$B$3,"",INDEX(Master!$E$1:$E$102,SMALL(IF(ISNUMBER(SEARCH($C$3,Master!$E$1:$E$102)),ROW(Master!$E$1:$E$102)-ROW(Master!$E$1)+1),E3)))

B3 = number of instances the word appears in the range
E3 = 1 (E4=2 E5=3 etc.)
Master = my data
C3 = The word I want to look up (partial text)

This formula returns all the instances of C3 in the Master Column E

Is there a way for me to return all instances of C3 in Master!A1:AX102 ?

Thanks for your help!

 

[Peter_SSs]

Welcome to the MrExcel board!

find all instances of a specific word in this range and return them to me in a list.

I can currently achieve this in a single column with this formula:

I don't know if it is important to you, but that current formula does not find instances of the word in C3 but instances of the string in C3.
That is, if C3 = "cat" the formula would return the cell contents of "The chooks scattered"

Anyway, assuming that you are happy with a string search, you could try this. Be aware though that INDIRECT is a volatile function so if used a lot, could impact your sheet's responsiveness.

Try this in row 3 and copied down (in Excel 365).

=LET(rng,Master!A$1:AX$102,IF(E3>B$3,"",INDIRECT("Master!"&TEXT(AGGREGATE(15,6,(ROW(rng)*10^6+COLUMN(rng))/ISNUMBER(SEARCH(C$3,rng)),E3),"R000000C000000"),0)))

Another option might be to use a macro to produce the list.

 

[kateie]

Welcome to the MrExcel board!

I don't know if it is important to you, but that current formula does not find instances of the word in C3 but instances of the string in C3.
That is, if C3 = "cat" the formula would return the cell contents of "The chooks scattered"

Anyway, assuming that you are happy with a string search, you could try this. Be aware though that INDIRECT is a volatile function so if used a lot, could impact your sheet's responsiveness.

Try this in row 3 and copied down (in Excel 365).

=LET(rng,Master!A$1:AX$102,IF(E3>B$3,"",INDIRECT("Master!"&TEXT(AGGREGATE(15,6,(ROW(rng)*10^6+COLUMN(rng))/ISNUMBER(SEARCH(C3,rng)),E3),"R000000C000000"),0)))

Another option might be to use a macro to produce the list.

Thank you so much this works!! Very much appreciate your help!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[kateie]

As so often happens, this solution has now presented a different problem for me and hoping you might be able to help with this as well!

My dataset is a calendar. I am trying to find all instances of events in a city in the calendar and then for it to return the date of the event which is in column A in my dataset.

Here's a screenshot of what my summary page looks like:

The formula earlier in this thread is helping me pull my data into Column F, however now I am trying to find the corresponding date to pull into Column D (the dates are in Column A in my master spreadsheet).

I am currently using this formula in Column D:

=IFERROR(INDEX(Master!$A$7:$A$102,SMALL(IF(F4=Master!$B$7:$AX$102,ROW(Master!$B$7:$AX$102)-ROW(Master!$B$6)),ROW(Master!1:1))),"")

Master!A1:A102 - Dates in the data tab
Master!B7:AX102 - Data

However when I pull the formula down, it is not giving me the correct results. I think it's getting confused because the cells in Column F are not the same. I think I need to be using a string search again and base the search around C3?

Sorry if this is vague and any tips would be very much appreciated!

Thanks.

 

[Peter_SSs]

Master!A1:A102 - Dates in the data tab
Master!B7:AX102 - Data

Can you clarify about why those red numbers do not match? What are the correct row numbers?

 

[kateie]

Sorry it should probably be A7:A102

Data starts in Row 7

 

[Peter_SSs]

Sorry it should probably be A7:A102

Data starts in Row 7

OK, try this.

D3:

=LET(rng,Master!B$7:AX$102,IF(E3>B$3,"",INDIRECT("Master!"&TEXT(AGGREGATE(15,6,(ROW(rng)*10^6+1)/ISNUMBER(SEARCH(C$3,rng)),E3),"R000000C000000"),0)))

F3:

=LET(rng,Master!B$7:AX$102,IF(E3>B$3,"",INDIRECT("Master!"&TEXT(AGGREGATE(15,6,(ROW(rng)*10^6+COLUMN(rng))/ISNUMBER(SEARCH(C$3,rng)),E3),"R000000C000000"),0)))

 

[kateie]

Peter it works thank you so much!! You've no idea how much you've helped me this is amazing!! Thank you!!

 

[Peter_SSs]

No problem. Glad to help.