Results 1 to 7 of 7

Thread: Vlookup problem

  1. #1
    Neophyte ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 365

    Vlookup problem



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

    I have a spreadsheet that is using VLOOKUP. I type a number in a cell and then 6 numbers fill in the cells to the right of the place where you type in a number. A person will be able to enter numbers 0- 40.

    My problem is that once I get past #11 it returns a #N/A. I am sure the fix is simple, but I have been looking at this all morning and I cannot find the error. Then all of a sudden I also had an issue with my first line for some reason. I typed #7 in there and it also returned the #N/A; however, when I typed #7 in another cell it worked jut fine.

    I am really only concerned about being able to get all 40 numbers to work. I am also only going to use five cells straight down in a column so they will not be going down a column from 0 - 40. After the spreadsheet is up and running I want to put in an Access database so they will only see those five cells.

    I have attached a copy of the spreadsheet that I am currently working with. Any help you can give me would be GREATLY appreciated.

    Thanks in advanced for your help.
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,273
    Articles
    0
    Excel Version
    Office 365 Subscription
    Your lookup range is only 6 rows deep.

    Change it to this:

    =VLOOKUP(J3,A$1:G$41,COLUMNS(A$1:B1),0)
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,675
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    Let's change things a bit and enter the values you want to be looked up in range J3:J8
    Select a range of cells containing the number of cells you want to return, J14:P14, say.
    Now in the formula bar, NOT IN THE FIRST CELL of J14:P14, enter =INDEX($A$1:$G$41,MATCH($J3,$A$1:$A$41,0),0) and confirm as an array formula with Ctrl+Shift+Enter (XL will add curly braces)
    Now you have a copy of the row you wanted
    Pull down for any other values neededNUMBERS TESTING SPREADSHEET.xlsx
    Thank you Ken for this secure forum.

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    887
    Articles
    0
    Excel Version
    Excel 2013
    Try (copy across)
    Code:
    =LOOKUP($J3,A$1:$A$41,B$1:B$41)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #5
    Neophyte ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 365
    EXCEL-lent and thanks for your help Ali!

  6. #6
    Neophyte ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 365
    Thanks Pecoflyer! I was trying to do an array, but I could only get it to work for one line at a time. You solved my riddle to include the whole spreadsheet and not just one line! Bravo!

    I do have one question though, and that is when I enter "0" that returns the #N/A. Do you know what might be going on there? Again, thanks soooo much for your help Pecoflyer!

  7. #7
    Neophyte ExcelHelpNeeded's Avatar
    Join Date
    Jul 2019
    Posts
    4
    Articles
    0
    Excel Version
    Excel 365
    Thanks Navic!

Posting Permissions

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