Forum Discussion
Vlookup to find multiple variations of a text string and return column
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.
4 Replies
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.
- VLOOKUP with last argument of FALSE always returns the first instance. To return the last instance, LOOKUP is the best. To return any instance between the first and the last, INDEX-AGGREGATE would return your desired result, including the first and the last.
- Have you tried advanced filter?
JKPieterse 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?
