+ Reply to Thread
Results 1 to 7 of 7

Vlookup nightmare!

  1. 06-20-2012, 11:37 AM #1
    Sph01
    Sph01 is offline
    Registered User
    Join Date
    10-10-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    21

    Vlookup nightmare!

    This is driving me mental, please help. Im sure i had nailed the V/HLookup function, but apparently not!

    I want to look up a value in a table array on another sheet, and return the value of the cell adjacent to it.
    However, i seem to keep returning a #N/A error.

    The formula is in cell K3 on 'Quotations Sheet'
    The lookup value is cell A3 on 'Quotations Sheet'
    The array is A3 to Q922 on 'Job Sheet'
    The lookup value appears in Column 2 of 'Job Sheet'

    I want to return the corresponding value of column 1, 'Job Sheet'

    this is my formula:
    =IF(I3="","",IF(I3="CANCELLED", "",VLOOKUP(A3,'Job Sheet'!$A$3:$B$922,1,FALSE)))

    I have trierd using the HLOOKUP function instead, to no avail.

    I suspect that it is due to the location of the value i wish to return in relation to the column in which the lookup value appears in the array.

    Any thoughts?
    Last edited by Sph01; 06-20-2012 at 11:59 AM. Reason: Post solved by rob.barnes01
    Reply With QuoteRegister To Reply
  2. 06-20-2012, 11:41 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup nightmare!

    So the value you have in A3 is for sure found in range A3:A922 of the Job Sheet?

    Is so, check for accuracy. Spelling, extra spaces, numbers formatted as Text....

    The formula looks good if you want to extract value from A3:A922.. which would be the same value that is in A3 of the Quotations sheet... if you want to get column B info, replace the 1 with a 2.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 06-20-2012, 11:44 AM #3
    rob.barnes01
    rob.barnes01 is offline
    Registered User
    Join Date
    06-19-2012
    Location
    N/A
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Vlookup nightmare!

    You can't do a VLOOKUP if the value you're trying to return is to the LEFT of your lookup value. An INDEX/MATCH formula might work for this:

    Please Login or Register  to view this content.
    This formula must be entered as an array formula by using CTRL-SHIFT-ENTER. I haven't tested this, so let me know if you have any issues with it.

    Hope this helps.
    Reply With QuoteRegister To Reply
  4. 06-20-2012, 11:48 AM #4
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Vlookup nightmare!

    You need to change the 1 just before the ,FALSE to 2 if you want to get the data from column B, but that isn't the cause of your problem. You will get the #N/A error if you are looking for an exact match (signified by the FALSE at the end of your formula) and there isn't one. If you think there is an exact match then it might be (if you are using numbers) that the numbers on one sheet are proper numbers, but the numbers on the other sheet are actually text values which just look like numbers. If you are looking up text values then you might have extra spaces in one sheet compared with the other.

    If you post an example file with the errors in then I can take a look for you.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  5. 06-20-2012, 11:57 AM #5
    Sph01
    Sph01 is offline
    Registered User
    Join Date
    10-10-2011
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Vlookup nightmare!

    Thanks for the rapid responses.

    rob.barnes01 your code works perfectly. Strange how the position makes a difference here if everything is technically accurate.

    Head scratching over, thank you!
    Reply With QuoteRegister To Reply
  6. 06-20-2012, 12:08 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup nightmare!

    I didn't pick up on the:

    The lookup value appears in Column 2 of 'Job Sheet'
    Reply With QuoteRegister To Reply
  7. 06-20-2012, 12:17 PM #7
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Vlookup nightmare!

    No, neither did I <bg>

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1