Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: VLookup is giving me #N/A error, help with data or formula?

  1. #1

    VLookup is giving me #N/A error, help with data or formula?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've searched the web and can't find a solution to my problem with excel mac 2011.

    My lookup_values are a combination of both text and numbers (they are manufacturer's part number in Column C).
    This is my formula =VLOOKUP(C4:C204,Sheet1!A1:AV86,34,0). I need Delivery Time which corresponds to the other worksheet in Column AH (C34)

    I believe the problem is with my data rather than my formula but not sure how to solve it. there's no trailing space, i cleared formats, etc. But I'm still getting the dreaded #N/A value.
    Ive checked if the values match with =B2=B2=True

    Hope somebody has a solution for me!! I've attached the sample data
    Attached Files Attached Files

  2. #2
    Your VLOOKUP formula is entered incorrectly.

    put this in C2

    =VLOOKUP(B2,Sheet1!$B$2:$BV$86,33,0)


    normally you could just copy this formula down your column, but you have what appears to be rows in between where you do not use the vlookup. You can just copy and paste the above formula into each cell and then just change B2 to match the cell it needs to look for the lookup value.
    Last edited by tommyt61; 2012-10-21 at 09:14 PM.

  3. #3
    holy bejeezus - that formula worked

  4. #4
    It worked up until Row 18
    =VLOOKUP(C18,Sheet1!B2:AC86,26,0)

    Now its not giving me a n/A error, its just showing me the formula in the cell only from then on ARGH.

    Can i send you the new attachment?

  5. #5
    Its the AA column on the middle tab, Now its just showing the formula, any idea what i'm doing wrong?

  6. #6
    How did you make your datarange become $ in the formula?

  7. #7
    I went down to AA34 you can pick it up from there.

    f4 key will add $ sign to cell range.
    Attached Files Attached Files

  8. #8
    Quote Originally Posted by tommyt61 View Post
    I went down to AA34 you can pick it up from there.

    f4 key will add $ sign to cell range.
    Do you know how to do that in a Mac? I saw on the tutorial F4 but it didn't work for my mac, do you know another way?

    PS, your formula is working brilliantly, I'm almost done, definitely know what went wrong and yeah - would like to know the F4 trick for macs!

    you've been a dream!

  9. #9
    Add $ to cell range on Mac

    Apple key + T

    at least that is what i was told.
    Last edited by tommyt61; 2012-10-21 at 10:46 PM.

  10. #10

Page 1 of 2 1 2 LastLast

Posting Permissions

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