Vlookup problem

ExcelHelpNeeded

New member
Joined
Jul 21, 2019
Messages
10
Reaction score
0
Points
0
Excel Version(s)
Excel 365
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.
 

Attachments

  • NUMBERS TESTING SPREADSHEET.xlsx
    12.2 KB · Views: 14
Your lookup range is only 6 rows deep.

Change it to this:

=VLOOKUP(J3,A$1:G$41,COLUMNS(A$1:B1),0)
 
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 neededView attachment NUMBERS TESTING SPREADSHEET.xlsx
 
Try (copy across)
Code:
=LOOKUP($J3,A$1:$A$41,B$1:B$41)
 
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!
 
Back
Top