Can anyone advise me how I do a VLOOKUP if a cell Contains a certain word. The cells contain various sizes which are either Standard, Secure or Secure Plus. Thanks
Printable View
Can anyone advise me how I do a VLOOKUP if a cell Contains a certain word. The cells contain various sizes which are either Standard, Secure or Secure Plus. Thanks
One way to do that is, with data in A1 =IF(ISERROR(SEARCH("Secure Plus",A1)),"",your_lookup_formula) Or another method = IF(ISERR(SEARCH("Secure Plus",A1)),"",VLOOKUP(A1,E1:F12,2)).
What I can assume that you are trying to find a numeric value in a column that might be text values, the solution is to convert the text value to search for the formula TEXT (value, format) or seek to convert the column where the value in numbers to do so, in a parallel column, enter = VALUE and drag the formula down until the last record, once done, copy and the first cell of the matrix Give original edit > paste special > values, it should work.
Like for example :
- Value to search in cell A1
- Matrix to look for A3: A30
- You will have a VLOOKUP like: = VLOOKUP (A1, A3: A30, 1, 0)
- Using the first option would you choose. = VLOOKUP (TEXT (A1, "0"), A3: A30, 1, 0)
- Using the second
- In C3 would write = VALUE (A3)
- Copy the formula from C3 to C30
- After selecting the range C3: C30 is copied, A3 and select Edit> Paste Special> Values. And it should work.
- Once completed you can delete C3: C30