Advanced vlookup – Text vs. Number

Why does vlookup not return the lookup value? I am sure it is in the source data table.  I see it by my eyes.
Have you ever experienced this?? That’s normal when you import your data from other database.  Fortunately it can be solved with a little trick.

Apparently, vlookup considers text string and number two different things.  Therefore “1” (as text) is different from 1 (as number).  Although we have “1” in the data table, the lookup_value we input is 1 (number).  In this way, Excel returns “#N/A” to mean you that the lookup_value is not available in the table_array.


In the source data table, the “Rank” is actually “Text”.  Note the little “Green Triangle” in the upper left corner of the cell.  Click on it, then you see the Smart Tag (the exclamation mark).  Click on the Smart tag and you see the message; and options.

If you convert the “Text” to “Number” from the source data table, you will have the correct answer as shown below:


However, I do not recommend you to convert the “text” to “number” in the source data.  Indeed, you should avoid changing anything in the data source unless you have a valid reason to do so.

It is easier to convert the lookup_value into “text”.  We may input with a leading ‘ (apostrophe coma) that tells Excel you are about to input “text” instead of “number”.

Another approach (recommended) is to do a simple trick in the formula as follow:


Trim is a text function.  By incorporating trim(lookup_value) in the formula, Excel will convert the lookup_value into “text”.  In this way, we are looking up “text” in a table_array with “text”.

In case the other way round, i.e. “number” in the source data while “text” as the lookup_value, we just need to convert the vlookup_value from “number” to “text”.  There are many ways to do so, e.g. by *1, by +0 (both basic maths give you exactly the same number as it was); or you may use value(lookup_value) to convert “text” to “number”.


Not difficult, right?  Just that you have to be clear about which one is “text” and which one is “number”. 🙂

Other topics about vlookup:

  1. The basic of vlookup
  2. Tips in constructing vlookup
  3. vlookup with Match 
  4. vlookup options – True or False?
  5. Advanced vlookup – Text vs. Number
  6. Advanced vlookup – Wildcard Characters “?” and “*”
  7. Alternative to vlookup – Index and Match
  8. Three different ways to do case-sensitive lookup

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , . Bookmark the permalink.

14 Responses to Advanced vlookup – Text vs. Number

  1. Igor says:

    I noticed long ago that Vlookup function is pretty fussy.
    I usually work with large tables of information, so inserting an ‘ apostrophe into each cell is not an option for me and format cell as text sometimes does not help. The only option which works for me is to go all the way to Data tab and use Text to Columns (select whole column) at the very end “Column data format” change “General” to “Text”.


    • MF says:

      You won’t need to do that now.
      Merry Christmas in advance! 🙂


      • Igor says:

        Merry Christmas to you MF as well (and to all readers)!
        In my “table_array” “lookup_value” stored as text so “value(lookup_value)” doesn’t work, but “trim(lookup_value)” does the work perfectly. Thank you so much for good advise.


  2. Ali says:

    How do you do a vlookup which looksup data irrespective of text or values.
    I believe somewhere I read you use
    -(dash) in between the formulae


    • MF says:

      HI Ali,
      Vlookup considers Text and Value are two different things. Depends on your lookup value and the value in the lookup table, you need to “convert” the lookup value to fit the lookup table.
      You may use TRIM to convert a value to text, or VALUE to convert a number stored as text to value.
      You may refer to the last 3 paragraphs of this post.


  3. Pingback: Vlookup number vs. text

  4. Pingback: Odd VLookup Results - Page 3

  5. Pingback: Alternative to vlookup – Index and Match | wmfexcel

  6. Pingback: vlookup – True or False?? | wmfexcel

  7. Pingback: vlookup with Match | wmfexcel

  8. Pingback: Tips in constructing vlookup | wmfexcel

  9. Pingback: The basic of vlookup | wmfexcel

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.