When I hit Enter, I get #N/A!. Also, the 'H:\ and the quote after Sheet1 disappear. I tried using the function wizard, but it doesn’t make any sense.
This is what I want to do: Compare two files. If the values in B2 through the end of the files match, insert the value in column E in H:/802489_Start_Dates.xlsx into the VLOOKUP column in the file I’m modifying.
So:
Where am I going wrong?
Why do the drive name and ending quote disappear every time I enter the code?
Before you try anything else, I’d suggest the advice I always give for VLOOKUP problems: try copying the sheets you want to compare into one workbook. Sometimes Excel is tetchy when you’re trying to VLOOKUP sheets on separate workbooks.
The exclamation mark goes between the sheet name and the range. Put the range you’re looking up in another sheet.
Seems to me the real problem–and Excel doesn’t make this clear so you’re not alone in this-- is that the data in your lookup range has to be in the first columns of that range. So if your vlookup range is $A$1:$Z$1000, your start date (if as I assume that’s what you’re looking up) has to be in column A. I think that might be the issue.
To sum up what everyone has said, here is an explanation of the syntax of a VLOOKUP function in Excel.
Lookup Value - looks like you understand this one. Directs to a string of text or a value that you would like to pin up against a table array. Be careful that the value you are trying to look up is in the same format as the table array you looking it up against. If one is formatted as text, and the other are values, you can run into some problems with Excel returning #N/A errors.
Table Array - The table array is the data you want to compare the lookup value to. As one user stated, it’s crucial that the lookup value is in the FARTHEST LEFT column in your table array. If the values you are trying to look up in the lookup value are in the second column of your table array, you’re going to get #N/A errors.
Column Number - The third argument is column number. This number represents the numerical column in your table array that you want the function to return. This number can not be larger then the total number of columns specified in your Table Array. So if argument 2, table array, you specify a table that has 4 columns - say A1:D4, then your column number can only be 1, 2, 3, or 4. This is the deciding factor of what will be displayed in the cell where you are actually placing the VLOOKUP function.
The last argument is optional and is called Range Lookup. It is either TRUE or FALSE. If it is not used, Excel will automatically set this argument to TRUE. Use true if want Excel to find the closest match to your lookup value found in the table array, and use false if you want Excel to find an EXACT match of your lookup value. In my experience, you’ll probably be using FALSE as an argument 99% of the time.
I didn’t save what I had last night, so I copied the ‘source’ columns into the file that needs the data. Since I didn’t do it exactly the same way as I did yesterday, the columns are different.
I want the dates to go into column R if the key matches. The key is in column T and the source date is in column U, beginning in row 2 (there is a header). So the code looke like this:
First question is do you have the same values in column B of the sheet on which you are doing the VLOOKUP and column A of the sheet where you are looking up values? As a few others have pointed out, these have to match in content and format, and these matching values must be the leftmost column in both the source and the target.
In your first example, you’re only going 5 columns over (from A to E), so I’m not sure why your second example, after copy/pasting, is calling for a value from 22 columns over. In general, it’s not a good idea to copy/paste a VLOOKUP since Excel will try to interpret everything in relative terms. I’ve done VLOOKUP using adjacent columns on a single worksheet, using different worksheets within a single workbook and using sheets in different workbooks. I don’t think copying your source data to a different location will make any difference.
After copying the ‘fix’ file, the key for the original file is in column A. The corrected date goes into column R. The key for the copied ‘fix’ file is in column T. The corrected date that is supposed to go into column R is in column U.
The goal is to create a file that has columns A through R, with column R being the corrected date, by matching the keys and moving the corrected date into column R when the keys match.
FWIW, this is now all academic. I’ve written sort and match Easytrieves (a sequential language) that takes all of the guesswork out of the obscure VLOOKUP code. It just has ‘IF MATCHED [this field = that field] END-IF’.
But I’ll still try any suggestions for future reference.
I think you’re confused about how VLOOKUP works. The 21 tells it where to read from, not to write to. So if you want the dates to go into column R, then you need to type this formula into column R:
=VLOOKUP(T2,$U$1:$$2500,,FALSE) where _ is the column letter and number you want to read from, so like:
=VLOOKUP(T2,$U$1:$W$2500,3,FALSE)
I guess I am. I thought column 22 (column U) is where I wanted to read from (i.e., the corrected date) and T2 is the key, and that VLOOKUP would write the result into the cell where the VLOOKUP formula is written.
So when I put ‘=VLOOKUP(T2,$T$1:$AU$2500,21,FALSE)’ I thought I was telling it to start looking for the key in T2, and if it matches anything in column A for 2500 records, write the value in column U (21) into column R (where the formula is).
The way you wrote that assumes that your source table goes from T1 to AU2500 so it would go to Column AM instead of U.
Since you wanted to Match from Column A you needed to put that in your source table.
I think your formula should have been (say you wanted to compare a list in column T starting with T2
In R2 you would right
=VLOOKUP(T2,$A$1:$AU$2500,21,FALSE)
So this says
go get the value in T2
find its match in Column A
Whatever row matches that T2 value stop there
Go over to the 21st column starting from Column 1 in your source (in this Case A) so column 21 is U
Get that value out of Column U
Display it here (Cell R2)
If you copy that formula down Column R it will compare each value in A to the same list and display the T match to that value
I put some bolding and size in my explanation above for emphasis.
BubbaDog: I think I actually hit on something like that yesterday. I was able to get dates, only they were not in the right place when I copied the formula to the bottom. I copied your formula just now (=VLOOKUP(T2,$A$1:$AU$2500,21,FALSE)) and copied it to the bottom of the file, and here’s what I have near the bottom of the file (edited for the Board):
You can see that 6170 has a correct date of 01/04/12; but the formula says it’s 04/20/00. Item 6181 has a correct date of 12/21/11, but the formula is making it 01/00/00 (which in addition to being wrong, is also invalid). 6186 exists with a date of 01/10/12, but the formula issues an invalid return notice.
Is the value that you are looking up contained in your range? IME, the first argument (lookup value) is NOT part of the range that you are looking up.
Cell “T2” is in the range $A$1:$AU$2500.
I usually design my lookup tables to look like this and my formula:
A B C
1 Month Sales Production
2 Jan 10,000 8,000
3 Feb 12,000 14,000
4 Mar 15,000 11,000
5 Apr 13,000 16,000
6 May 15,000 15,000
7 Jun 22,000 21,000
11 Feb 12000 =VLOOKUP(A11,$A$2:$C$7,2,FALSE)
Notice, my first argument in the Vlookup statement is NOT part of the $A$3:$C$8 range
Yes. I want it to look in column T for the key. T2 exists between A1 and U2500.
Yes, they are formatted as dates. (One of the many things I hate about Excel is that it assumes that it knows what the user wants better than the user.)
