May 29 2019 08:58 AM
May 29 2019 08:58 AM
This is what I have so far:
=VLOOKUP("*"&$H$2&"*", Datatable, COLUMN(A2), FALSE)
$H$2 = Contains the text I'm searching for within Datatable
When I run the formula, I only get the first instance of the text. However, I know there is more than one case that contains this text within the Datatable.
Please help. I feel like I'm so close to getting this to work.
May 29 2019 10:09 AM
@Jan Karel Pieterse Yes, I have. It returns either all the data or a few items that don't contain the information I'm looking for. Could that be because the information is within a string of text?
May 29 2019 10:11 AM
May 29 2019 01:20 PM - edited May 29 2019 01:23 PM
There are a number of ways of achieving this with formulas.
One is to use SEARCH to determine whether the search string is contained within the target list. Within a table this might be performed record by record using a helper field
= ISNUMBER( SEARCH(searchString, [@targetString]) )
or it would appear slightly differently within an array calculation, where
= ISNUMBER( SEARCH(searchString, targetString)
gives the entire column of results either using CSE or Dynamic Arrays (Office 365 insider only at present). I often place array calculations within a named formula where they may be referenced and calculated on demand (here I might use the name 'matches?').
The dynamic array calculation would then proceed with
= FILTER( Table1, matches? )
which returns every matching record.
With standard versions of Excel, things are a little more turgid. I tend to define a further named array 'k' to hold record numbers.
= ROW(Table1[targetString]) - ROW(Table1[#Headers])
[the formula is so pig-ugly it needs to be hidden but, in future, SEQUENCE will do a much better job]. The formula
= IF( matches?, k )
gives the row numbers for matched records padded with FALSE for failed matches, and then SMALL reduces the list to consecutive values
= SMALL( IF( matches?, k ), k ),
which can be used by INDEX to return the required records.
A completely different strategy is to use MATCH to find the first record number and then to use INDEX to return the first cell of the remaining search field. The second MATCH operates on the residual part of the table, and so on.