I use VLookup and get #N/A error.
I'm using Excel 2002 and I have used VLookup for a markbook but it gives me a #N/A error. There are three marking criteria (KAPS, MAPS and CAJ) and when those three cells are filled in with a mark (A+ to E-), I wanted the end result to show up as an average (Example: A for KAPS, C for MAPS and A for CAJ = B+ overall). I can attach my markbook if anyone wants to view the problem in more detail. I didn't expect this to be so complicated... or is there something I missed?
Last edited by Sabrith; 03-24-2013 at 01:42 AM.
The workbook you posted does not have a sheet named: =Conversion
for use in the formulas: =VLOOKUP(D4,Conversion!$D$1:$E$15,2,FALSE)
Ben Van Johnson
Ok I'll add the sheet.
What Ben is trying to tell you, is that your formula is trying to reference a sheet that doesnt exist in your workbook. If you just "add the sheet", unless it has the data that the formula is trying to reference, it still wont work
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
So now I have the sheet called 'Conversion' and I have the data filled in on that sheet too. But I think I'm still missing something because it still wont work.
your formula is looking for something in D4...IF that is the correct reference, there is nothing in D4, therefor it has nothing to search for.
I would suggest that you do a bit of trouble-shooting on your own 1st, before posting more questions here
I think the D4 is referring to the information that needs to be put in specifically by the user. Example: It is blank until you choose anything from A+ to E-, then it should appear in the cells bellow (row 58). But it still doesn't show up even if I write a mark. I've been at this all day so far and it is starting to frustrate me. I'll mess around with it until I can come up with something.
I changed your formula, use this, copied down and across...
=IFERROR(INDEX(Conversion!$D$1:$E$15,MATCH(Marks!D4,Conversion!$D$1:$D$15,0),2),0)
Sorry, but where do I put the formula? E58/F58/G58 and below? Or H58/I58 and below? I don't use Excel very often so I'm confused...
Sorry. copy it to E58:G81
All it does is change everything to #NAME?.
works for me, see the attached
I know whats going on. I'm using an older version of Excel. Thanks for the formula though, I will use it when I get back on a better computer with a newer Excel. So this is pretty much solved?
OK that would do it. I saw the .xlsx extention and assumed you were using 2007 or later, didnt even look at your profile. Try this for older versions of excel...
=IF(iserror(INDEX(Conversion!$D$1:$E$15,MATCH(Marks!D4,Conversion!$D$1:$D$15,0),2),0,INDEX(Conversion!$D$1:$E$15,MATCH(Marks!D4,Conversion!$D$1:$D$15,0),2)
It says it has an error, so I'll just use your formula for the newer excel later.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks