LinkBack URL
About LinkBacks
I'm trying to use a basic formula, but it returns an N/A
=VLOOKUP(F12,A2:B999,2, TRUE)
However, it works if I plug in the value manually
=VLOOKUP("4000000", A2:B999,2,FALSE)
Why is this, and how can i fix it?
I'm trying to use a basic formula, but it returns an N/A
=VLOOKUP(F12,A2:B999,2, TRUE)
However, it works if I plug in the value manually
=VLOOKUP("4000000", A2:B999,2,FALSE)
Why is this, and how can i fix it?
It works when I try it. Can you post some sample data so we can see the error in action?
Last edited by JBeaucaire; 11-10-2008 at 09:21 PM. Reason: added a workbook sample
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Why are you using TRUE as your final parameter? False is usually better as it can cope with unsorted data.
Ed
_____________________________
Always learning, but never enough!
_____________________________
Could it be that values in the second column of A2:b999 are text strings looking as numbers, rather than numbersOriginally Posted by eldoradotower
If so you might try =VLOOKUP(F12&"",$A$2:$B$999,2, false)
( Using False as EdMac suggests)
Adding a sample of your data as an xls file will give a clearer view on your problem
As Arthur says, it looks like the numbers are, actually, text values - the fact that you have put " " around the lookup value would seem to confirm this.
Take the speech marks out and see what happens - if it fails than this is your problem. Keep the last parameter as false as previously advised.
Post a copy of your sheet and then it can be looked at in detail.
http://www.contextures.com/xlFunctions02.html
gives some good examples of troubleshooting vlookup for future reference
ok thanks guys, it worked...i need to sum the columns but when i drag down the formula the cells don't change
=IF(ISNA(VLOOKUP(F13&"",A3:B1000,2,FALSE)),0,VLOOKUP(F13&"",A3:B1000,2, FALSE))
is it possible to make the above formula change appropriately when dragged down?
Could you post a sample of your data as already asked? This would be helpful Thx
You need to change the cell references to absolute references so that they do not change when you drag them down.
I DO want the cells to change. And I can't really post sample data because its all financial information from an ms query.
Do you mean the references don't change or that the result is not changing?
If the latter, than go to Tools|Options and from the Calculation tab, select Automatic calculation.
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.
the references
works ok for me dragged down looks like this
you should fix the $A$3:$B$1000 absolutely as the referenced table is not changing but f13 goes to f14 and so on
Please Login or Register to view this content.
thanks! please mark thread solved
You can do this yourself.
Please edit your first post - Click "Go advanced" and modify the prefix of your title
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks