Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to use VLOOKUP IF in cell that contains Text

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 11-01-2012
Member
 
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

Reply With Quote
  #2  
Old 11-01-2012
Member
 
Join Date: May 2011
Posts: 515
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)).
Reply With Quote
  #3  
Old 11-01-2012
Member
 
Join Date: Jun 2011
Posts: 780
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
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to use VLOOKUP IF in cell that contains Text"
Thread Thread Starter Forum Replies Last Post
How to get multiple values of Vlookup in Single Cell Henriksen MS Office Support 4 25-02-2012 10:02 AM
Using VLookup to give Hyperlink to a cell in another worksheet AlpAnA$ Software Development 4 13-07-2011 09:34 PM
Number stored as text in vlookup Alexander102 Windows Software 3 09-06-2009 09:00 AM
How to change cell formula to text in VBA Chhaya Software Development 3 08-05-2009 12:12 PM
Auto-populate a MS Word table cell with text from a diff cell? dreamrthts Windows Software 5 23-03-2009 10:39 PM


All times are GMT +5.5. The time now is 09:37 AM.