Why does the following formula give me a #NA result?
=VLOOKUP(B2,Anthem!A2:A107
I'm using Excel 2003. I know the information in the referenced cell is in the range - I've checked it manually. The data is sorted ascending. I've made sure the cell formats are the same...very frustrated. Any ideas?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Here's the file. I couldn't send the original as it contained HIPAA protected information so I've stripped that out.
Basically what I'm trying to accomplish is to have a separate worksheet for every fee schedule and have the appropriate amount pulled into the first worksheet based on the procedure code.
Please let me know what you think and what it is I'm missing! My head hurts from banging it on the screen :)
Your values in Anthem sheet have a extra space where as the values that the values in your sheet doesnt have that space. So you need to go ahead and trim those values and then also need to change the vlookup formula as you have applied it incorrectly and post that it will do what you are looking for..
Enclosed is the file for your reference..
Saurabh...
OK. That got me part of the way :) A few are looking up the amounts correctly. It appears that some of them are #NA because they aren't in the list which I would expect. I suspect the rest of them are getting the #NA because there is more than one matching result in the first column. Can I nest a vlookup or use and if function to tell it to use the number based on what's in the second column (modifier)? Also, can I tell it to leave it blank if there isn't a match?
Thanks, saurabh726! That got me much further along and most of the data is pulling correctly now. I was able to sort the Anthem worksheet so that it pulls the correct amount (the one that has 00 as the modifier. The only other thing I need now if to figure out how to apply the if function so that it leaves it blank if it doesn't find a matching value.
OK, saurabh726! That got me even further but when I went to duplicate the formula for another fee schedule, it doesn't work for all fields...what am I missing this time? (FYI, I did switch to named ranges - i thought it would be simpler.)
Revised file attached. Thanks for all of your help! I'm learning by the pound!
Hello mpconroy,
Are you talking about the formula in Sheet1 column C? The reason the formula doesn't work for you is that the values in column B are text formatted whilst the Standard values are numbers, you can get round that by using this formula in C2 copied down:
=IF(ISNA(VLOOKUP(B2+0
The +0 converts the numbers to text. Note: that formula would only be appropriate in this exact situation. A better long-term solution is to convert the text values to numbers (or vice versa), to do that select column B in sheet 1 and use
Data > Text to columns > Finish
Now you only need the regular formula in C2, i.e.
=IF(ISNA(VLOOKUP(B2,ST
N
regards, barry
Thanks, saurabh726! I thought that might be the case as I pulled that information in the STANDARD worksheet came from the same datasource as the CPTCODE column on the first sheet. I can't see how you trimmed the data so that I can repeat the proces. It's not something I have had a lof of experience with - obviously :) Any special trick that?
Thanks again for all of your help! This has become a huge priority and it is greatly appreciated!
Business Accounts
Answer for Membership
by: saurabh726Posted on 2009-03-30 at 09:32:36ID: 24020507
Can you plese update your sample file along with the question as there can be numerous reasons why its giving you an incorrect result rather then giving the answer.