The #N/A formula was due to the fact the match item in Sheet A had an extra space at the end... the matches have to be exact so delete that space....
Also, instead of entering the string into the formula, why not just reference the item in column A of sheets B and C....
e.g. instead of =VLOOKUP("SEPCM00024823",'Sheet A'!$A$2:$C$8,3,0) try using =VLOOKUP(A2,'Sheet A'!$A$2:$C$8,3,0) copied down.
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.
Hello NBVC
Thanks again ...If i use just reference the item in column A of sheets B and C and not enter the string into the formula then in worksheet "B" it is taking the refrence of "A2" column of worksheet "B" only and not of the worksheet "A".
Similarly in worksheet "C" it is taking the refrence of "A2" column of worksheet "C" only and not of the worksheet "A".
but if i enter the string into the formula then then it seems to be working ...
kindly let me know whats the problem .......as i myself don't wana use string into the formula...kindly help!
Thanks again for helping me out.
You should only enter the formula in the first row (C2) and then copy down the formula, this will automatically change the reference to column A to coincide with the next entry in A.
To copy the formula down, click and hold the little black square at the bottom right corner of the active cell and drag it down the column.
See attached.
Hello NBVC -
Perfect ...thats what i was looking for and it helped .But one more issue related to the same, as in Worksheet "B" there are some entries in column "A" which are not present in column "A" of Worksheet "A" ,then i want to keep the value of column "C" in Worksheet "B" intact.
Currently if i use =VLOOKUP(A2,'Sheet A'!$A$2:$C$8,3,0) in worksheet "B" under column "C" then for unmatached records it is displaying the value as "#NA".
I want to search the value of Column "A" of worksheet "B" in Column "A" of worksheet "A" and if a matach is found then update the value of Column "C" of worksheet "B" otherwise keep the value of Column "C" of worksheet "B" as what is mentioned.
Please help.
Thanks again for helping me out.
Unfortunately, Excel does not allow you to choose a formula result or leave the cell "untouched" (unless you get into programming it with VBA).
What I can suggest is that you include another column with a new formula that will look for a match first and if it is not found, then it will take from the original State column.....
See sample attached...formula in D2 copied down is:I hope this works for you
=IF(ISNA(VLOOKUP(A2,'Sheet A'!$A$2:$C$8,3,0)),C2,VLOOKUP(A2,'Sheet A'!$A$2:$C$8,3,0))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks