+ Reply to Thread
Results 1 to 5 of 5

#N/A with VLOOKUP

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    17

    Question #N/A with VLOOKUP

    I have an array with columns I and J. I contains two digit numbers and J contains text.
    Column E in worksheet contains five digit numbers. I want to pick the text i J column depending the first two digits in column E so this is what I did:
    VLOOKUP(LEFT(E9,2),$1$I:$J$25,2,FALSE) and I get #N/A error but if I put in a number instead of the substring
    VLOOKUP(11,$1$I:$J$25,2,FALSE) it works.
    The E and I columns are formatted as NUMBERS. I have tried GEBERAL and there is no change.
    Any suggestions?

  2. #2
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: #N/A with VLOOKUP

    =vlookup(left(e9,2)-0,$1$i:$j$25,2,false)

  3. #3
    Registered User
    Join Date
    01-27-2014
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: #N/A with VLOOKUP

    Yes, right, but why the minus zero?

  4. #4
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: #N/A with VLOOKUP

    all value obtained from left/right/mid is number value in text format, not numerical value. text value-0 or /1 or *1 will force it back to numerical value.

  5. #5
    Registered User
    Join Date
    01-27-2014
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2016
    Posts
    17

    Re: #N/A with VLOOKUP

    Valuable info. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 12-07-2016, 09:18 AM
  2. Using vLookup based on results from a vLookup & returning an undetermined list
    By NormalityBan in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-14-2014, 05:02 AM
  3. Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  4. Display Cell within VLookup range that excel thinks matches the VLookup Value
    By headachexcelperson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 04:56 PM
  5. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  6. Replies: 5
    Last Post: 07-29-2009, 07:53 AM
  7. vlookup function-Can vlookup command find the data from the 5 sheets.
    By Rishab shah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1