I'm using VLOOKUP to create a Cross Reference tool. CR is my "anchor" page that I'm attempting to tie the remaining worksheets WIT,TEC,COP back to the CR worksheet. I'm able to get VLOOKUP to work on the CR worksheet in columns H & I but unable to get the VLOOKUP to work in column J. The VLOOKUP function is entered but it does not return a value that I know exists in worksheet COP.
Would really appreciate someone letting me know what the problem is, thank you.
Last edited by nander; 03-24-2008 at 05:10 PM.
Don't understand why you would want to use a VLOOKUP in the false portion of this to return the value that you want to lookup in the first place; the ValueIfFalse argument could just be A2.
That aside, the error is that the values in A have a space after them, but in COP, they don't, so the values are not technically equal.
It is what I understand to do. How would you suggest I remove the space after the item number?
Highlight the column, Ctrl+H, type a space in the first box, leave the second blank, and click Replace All.
If you have time to show a more effecitive function please be my guest. I don't mind the contructive critisism.
=IF(ISNA(VLOOKUP(A2,COP!A$2:$A$108,1,FALSE)),"",A2)
I changed the range from A:G to be only column A, as you are only looking up the first column, removed the space in the Value_If_True, and changed the Value_If_False argument to simply be the value you were looking up. This can be put in J2 and dragged down.
Edit: you will have to remove the space between A2 and the ). This forum adds a space to formulas over 50 characters.
Last edited by darkyam; 03-24-2008 at 12:49 PM.
Okay, in the CR worksheet I removed the space at the end of the item# using the ctrl-H. I also copied and pasted the formula to the cell in J2 and copied and pasted this down the column but the values are blank.
Why would my original formulas work on the CR worksheet in columns H & I but not J?
It worked just fine for me. Can you post your workbook as it is now?
H should also be amended to =IF(ISNA(VLOOKUP($B2,WIT!$A$1:$A$80,1,FALSE)),"",B2, but as this formula was not originally in question, I hadn't bothered looking at it until now.
J should be left as is because you are not looking up the first column for that lookup value, but the third.
Last edited by darkyam; 03-24-2008 at 02:28 PM.
Okay here is my file. I've tried adding functions to pull additional data from the COP worksheet and the TEC worksheet. I inserted some columns for the TEC data on the CR worksheet. I've attached the file
Last edited by nander; 03-24-2008 at 05:11 PM.
You appear to have Automatic Calculation turned off. Either change that in your settings or hit F9 to recalc the sheet.
Okay, turning on the Automatic calcualtion fixed the problem. Also the space after the item# was also to blame. Thanks so much for helping me figure this out. I got tired of banging my head against the wall.
Last edited by nander; 03-24-2008 at 05:16 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks