1. ## Vlookup problem

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.

2. Your lookup range is only 6 rows deep.

Change it to this:

=VLOOKUP(J3,A\$1:G\$41,COLUMNS(A\$1:B1),0)

3. EXCEL-lent and thanks for your help Ali!

4. 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

5. Try (copy across)
Code:
`=LOOKUP(\$J3,A\$1:\$A\$41,B\$1:B\$41)`

6. Thanks Navic!

7. 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!

#### Posting Permissions

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