Is there a way that if VLOOKUP doesn't find a match that it can return the original value that was in the column? We use a file that we update information in. If we do not have an update, i do not want VLOOKUP replacing the data where there is no match. I want it to leave the last information. Please help there must be a way to have VLOOKUP, IGNORE cells where it does not find a match.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
VLOOKUP help needed ASAP please, do not want #NA...
- Thread starter apravato
- Start date
KleptoOne
Board Regular
- Joined
- Nov 18, 2004
- Messages
- 111
I'm not sure how you have your data arranged, but if you have a database in A1:B5, the value you are looking up in column A and the return value in column B, and the value you are looking up is in D1, then try this formula
Hope this helps.
Code:
=IF(ISNA(VLOOKUP(D1,A1:B5,2,0)),D1,VLOOKUP(D1,A1:B5,2,0))Hope this helps.
Upvote
0
Didn't work. What I am trying to do is this. Lets says in workbook #1 I have data in Column A called "DATE", and column B "new update text". In Workbook #2, Column C is Date and column D is called "UPDATED TEXT". Updated TEXT has text in some of the cells in workbook 2. These are the last updates we had that we entered and until we have new updates from workbook #1, these need to stay there. I want to vlookup updated text from workbook 1 and match with workbook 2. Workbook 2 may have 1000 lines and workbook 1 may only have 50 lines. I want only the 50 new updates to overwrite in workbook 2 and leave the others where vlookup did not find a match. basically ignoring any data in the destination cells where there was no vlookup match. Again my destination cells already may have data in them., that I want replaced if there is a match or left alone if not. Hoepfully someone can help.
Upvote
0
KleptoOne
Board Regular
- Joined
- Nov 18, 2004
- Messages
- 111
Ok, so what you actually want is not really a vlookup then, more like a macro that will go through and update a database with updated entries. If you do not want to use VBA then I would need to know if the updated text was a reference link, or simply typed in..
Would you be willing to use a VBA solution?
Would you be willing to use a VBA solution?
Upvote
0
I am not sure what a VBA is. But I'll use whatever gets the job done quickly.
Again, I have data in one large file. Let's say 1000 lines, sorted by PO#. There is column called "Status". Let's say we have 10 suppliers making up the 1000 lines, so each supplier has 100 lines. The supplier will send us their status for their lines in a separate excel file. The common denominator is the PO#. I want to quickly be able to put their 100 updates into the master file, which would overwrite their prvious updates on the Po's with the new information. When the next supplier sends their stuff, i would do the same. Right now people are manually typing in line by line. We actually deal with a file that has over 20,000 lines, so very cumbersome. If we could just quickly match the PO #'s between files and automatically add the new data that would save MUCH time!! Thanks in adavnce for any help.
Again, I have data in one large file. Let's say 1000 lines, sorted by PO#. There is column called "Status". Let's say we have 10 suppliers making up the 1000 lines, so each supplier has 100 lines. The supplier will send us their status for their lines in a separate excel file. The common denominator is the PO#. I want to quickly be able to put their 100 updates into the master file, which would overwrite their prvious updates on the Po's with the new information. When the next supplier sends their stuff, i would do the same. Right now people are manually typing in line by line. We actually deal with a file that has over 20,000 lines, so very cumbersome. If we could just quickly match the PO #'s between files and automatically add the new data that would save MUCH time!! Thanks in adavnce for any help.
Upvote
0
Similar threads
- Solved
