Results 1 to 3 of 3

Thread: How to use VLOOKUP IF in cell that contains Text

  1. #1
    Join Date
    Jan 2012
    Posts
    17

    How to use VLOOKUP IF in cell that contains Text

    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

  2. #2
    Join Date
    May 2011
    Posts
    526

    Re: How to use VLOOKUP IF in cell that contains Text

    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)).

  3. #3
    Join Date
    Jun 2011
    Posts
    804

    Re: How to use VLOOKUP IF in cell that contains Text

    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

Similar Threads

  1. How to get multiple values of Vlookup in Single Cell
    By Henriksen in forum MS Office Support
    Replies: 4
    Last Post: 25-02-2012, 10:02 AM
  2. Using VLookup to give Hyperlink to a cell in another worksheet
    By AlpAnA$ in forum Software Development
    Replies: 4
    Last Post: 13-07-2011, 09:34 PM
  3. Number stored as text in vlookup
    By Alexander102 in forum Windows Software
    Replies: 3
    Last Post: 09-06-2009, 09:00 AM
  4. How to change cell formula to text in VBA
    By Chhaya in forum Software Development
    Replies: 3
    Last Post: 08-05-2009, 12:12 PM
  5. Auto-populate a MS Word table cell with text from a diff cell?
    By dreamrthts in forum Windows Software
    Replies: 5
    Last Post: 23-03-2009, 10:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •