問題描述
如何檢查單個值以查看它是否包含 18 個值之一 (How do I check a single value to see if it contains one of 18 values)
I'm trying to do a backwards vlookup.
Have: Sentences (string) Looking for: Keywords (array)
I know I could do a rather crazy compound statement with if(iserr(find("Missing",B1,1)),if(iserr(find("Located",B1,1)),..
But considering I need to use this 1,000 times, and the if then's would be 18 deep. that just doesn't make sense. I know I can do this with VBA pretty clealy, but I can't seem to figure out the syntax.
Here's what I have so far:
Function FindValue(ByRef strToSearch As String, rngLookUpValues As Range) As String
On Err GoTo err_capture
'strToSearch is the sentence I am searching
'rngLookUpValue is a two column Range.
' The first column is what I'm searching for. If it exists in the sentence,
' return the second column
' The second column is the category that applies when the word from column one
' is found in the sentence
i = 0
For Each row In rngLookUpValues
i = i + 1
If InStr(1, strToSearch, row.cell(i, 1).Value, vbTextCompare) > 0 Then
FindValue = row.cell(i, 2).Value
End If
Next
Exit Function
err_capture:
Debug.Print Err.Number & ": " & Err.Description
End Function
When I run this it returns a #Value. But when I debug it or watch it run, no errors. It just dies during the instr() function.
‑‑‑‑‑
參考解法
方法 1:
No need for VBA, an array formula will do:
=IFERROR(INDEX($B:$B,MATCH(TRUE,FIND($A:$A,D1)>0,0)),"")
where column B is the category, column A the list of words and D1 the sentence to check.
Note: Enter the array formula with Ctrl‑Shift‑Enter!
For Excel 2003 and your particular example, use this formula:
=IF(ISERROR(MATCH(TRUE,FIND('Intake Chart'!$A$2:$A$18,E26)>0,0)),"no match",
INDEX('Intake Chart'!$B$2:$B$18,MATCH(TRUE,FIND('Intake Chart'!$A$2:$A$18,E26)>0,0)))
Take a look at this file ‑ it has exactly the same structure and the working formula.
(by Lee_Str、Peter Albert)