#1
|
|||
|
|||
Search with multiple strings
Hello, I have a search list called categories with each item in the list having a rank:
A2: employee safety A3: end of life A4: life A5: lifespan A6: patient safety A7: quality B2:3 B3:1 B4:4 B5:4 B6:3 B7:2 I am using this formula to return the value found in the string being searched "End of life span", {=INDEX($A$2:$A$7,MAX(IF(ISERROR(SEARCH($A$2:$A$7, S11)),-1,1)*(ROW($A$2:$A$7)-ROW($A$2)+1)))} The formula returns "life" but I want it to return "End of life", I tried using min in the formula, but to no avail. I need this to use Vlookup to find the rank of the item found, as the "ranks" will be tallied and scored. Any help is appreciated. |
#2
|
||||
|
||||
Try:
=OFFSET($A$2,MATCH(S11,$A$2:$A$7,0)-1,0)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
HI
thanks for the new idea, works on a sorted list w/the [match_type] left off. You'd think an exact match would work! And I realized afterward, I can use the index function to find the rank negating the need for the vlookup. d |
#4
|
||||
|
||||
The MATCH function works regardless of whether the list is sorted. The match type = 0 prevents false matches. And, if you want the rank that corresponds with a given category, simply use:
=OFFSET($A$2,MATCH(S11,$A$2:$A$7,0)-1,1) You can use the same logic to find which category corresponds with a given rank. For example, with a given rank in S12: =OFFSET($A$2,MATCH(S12,$B$2:$B$7,0)-1,0) Of course, where you have multiple values with the same rank, this only reports the first one. The formulae for finding the second & subsequent instances of the same rank are rather more complicated.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks again for taking the time to reply. This has been an interesting challenge. I've attached a worksheet with both formulas as you've suggested. The intent of this worksheet is to score each of the highlighted columns using this formula. As you can see it is not working as planned, there must be something I'm not doing right or something I've missed telling you as it does on paper seem fairly straight forward. I think what I missed telling you is I am searching for a string within a string which Match may not be able to do? The search string would contain more than just the item being searched for.
thanks again d Last edited by silverspr; 03-03-2013 at 03:03 PM. |
#6
|
||||
|
||||
The simple answer is that what you've input into S11, for example, doesn't match any of the entries in A2-A7. In S11, you have 'End of Life span', but that string doesn't exist in A2-A7.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Thanks again
I thought as much, then the search function is a better option? Which brings me back to my original problem, depending on how the list is sorted the function returns "end of life" or "life" with the preferred option being "end of life". thanks I will have to muddle through this... |
#8
|
||||
|
||||
The problem with that approach is that you're likely to end up with false matches.
By trying to match an array that only contains sub-string(s) of the strings you're trying to match on, you are, in effect, asking the array to search the search string before it can tell you which search returns a match. Without a lot of work, that approach is only ever going to return the first of any possible number of matches. To do that, it's probably best to employ a User-Defined Function, such as the following, which you can add to an ordinary code module in the workbook: Code:
Function MatchSubString(StrVal As Variant, Arr As Range, Optional MatchCase As Variant) As Variant Dim i As Long, j As Long, k As Long, l As Long Dim StrArr As String, StrItm As String, Rng As Range For Each Rng In Arr.Cells StrArr = StrArr & Rng.Text & "," Next If MatchCase = 1 Then MatchCase = vbBinaryCompare Else MatchCase = vbTextCompare End If MatchSubString = "N/A" For i = 0 To UBound(Split(StrArr, ",")) StrItm = Split(StrArr, ",")(i) If InStr(StrItm, StrVal) > 0 Then MatchSubString = i + 1 Exit Function ElseIf Len(StrVal) > Len(StrItm) Then For j = 1 To Len(StrVal) If InStr(1, StrItm, Left(StrVal, j), MatchCase) > 0 Then If j > k Then k = j MatchSubString = i + 1 End If End If Next End If Next End Function =MatchSubString(S11,$A$2:$A$7,0) or =MatchSubString(S11,$A$2:$A$7,0) would return '2' for "End of life span" in S11. You can nest the function in other Excel functions, too: =OFFSET($A$2,MatchSubString(S11,$A$2:$A$7,0)-1,1)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
multiple strings, search |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
search on multiple word documents | Guy Roth | Word | 7 | 03-06-2017 01:31 PM |
Find, aka search aka ctrl+pgdn across multiple lines | snarfii | Word | 1 | 04-22-2012 09:56 PM |
Autotext strings are being stripped of their format when called from VBA | JEilerts | Word VBA | 2 | 04-21-2012 07:49 AM |
Removing Character Strings | digitalhecht | Word | 2 | 10-17-2011 12:53 PM |
update style of all strings available between two specific strings | vikrantkale | Word | 1 | 03-28-2011 06:13 PM |