I’m trying to fill in some data using this VLOOKUP formula:
=VLOOKUP($A10912,[Pace_File_2.xls]Sheet1!$A$1:$Z$15000,6,FALSE)
It worked for some cells but not for all of them. The target file has about 11,000 records, and the source file has about 12,500 records.
Can someone please point to what I’m doing wrong?
First of all, I’m not sure what you mean when you say it worked for some but not all of your records. If the vlookup returns “0” that means the source cell is blank, and if it returns “#N/A” that means the target reference was not found in the source file. Are you getting some other result?
Beyond that, I have a couple of other questions. Are you actually starting this lookup at A10912? That seems a bit odd. Whenever I’m doing a vlookup, I’m usually starting at the top of the file. Second, I’m not sure I understand why your source range is all the way from A to Z, if your reference point is in column F (the 6th column from your starting column).
Is The first column in the source file (Table) in alphabetical order?
That’s a must with Vlookup.
I should have been more clear. For most of the cells, the correct value (City) was returned. For many of the cells, it returned #N/A.
Yes, I was starting at record 10912. The City field was populated in most of the file, so I sorted on that field and started coding there. If the value in column A matches, then put the City from column F into column F. I go from A to Z because I don’t know how to use VLOOKUP and I just have a ‘shell’ that I copied. (Normally I’d run the data through a sequential program, but in this case VLOOKUP is better than writing a Sort and a Match program.)
FWIW, I’m looking up the #N/A cities by their ZIP codes and copying or typing them in. So I’ll get done what needs to be done. I’m still curious though, about why I get the #N/A for some cells (that are populated in the source file) and I get the correct values in most cells.
Yes, the source file is completely sorted by column A. The target file is sorted on column A where the records contains VLOOKUP.
One possibility that has caused me a few headaches – trailing blank spaces. It will look like you have the same value in Column A in both the source and the target file, but one of them has trailing blank spaces. To check for this, go to one of the cells that should have produced a result and check the value for column A in both files.
By the way, one relatively easy way to do a vlookup is to click on the function symbol, then use the popup to select vlookup. From there, you can use the mouse to fill in your parameters. Put the cursor in the first box, then use the mouse to select the first cell you’re using as your reference point. That will fill that cell’s location into the box. Then move your cursor to the second box and use your mouse to highlight columns in your source file, starting with the column that contains your reference value and ending with the column that contains the data you’re trying to look up. Then just move the cursor to the third box, type in the number of that column relative to the starting location, and finally type False in the fourth box.
=VLOOKUP($A10912,[Pace_File_2.xls]Sheet1!$A$1:$Z$15000,6,FALSE)
As the formula is written, the #N/A means that you were looking for an exact match, and there was no exact match. So are you sure that your input (to be matched) actually has a matching entry in the target column? I bet there are legitimate non-matches. That has been a problem in my use of VLOOKUP in the past.
That didn’t occur to me. I checked, and the number I looked up did not exist in the source file. :smack: Supposedly the source file contains all of the records that are in the target file (plus a few thousand others), only with City populated. The sender apparently is telling fibs. All the more reason I’m going to have her QC the reformatted final output file after I write the program.
Thanks.
By the way, I should add that when I first started the job I’m in now, I had never used vlookup before. For the first 6 months, I despised it, mostly because the terms Excel uses are almost meaningless. But once I developed a better understanding, I began to realize that it’s really a useful tool, and not nearly as awful as I originally thought. So hang in there and good luck!
I tried to use it about four years ago, and it would take forever to finish – if it finished at all. Once I left the computer on for 12 hours, and the cells were never filled in. We have new computers now, with the latest OS. Now I can use it when I have a need to. But it’s not something I need very often.
First, be sure the lookup data is sorted.
Maybe you can use the TRIM function to remove trailing blanks.
(make a formula say AA1 is =TRIM(A1) for all column AA in lookup table, then take the column AA and paste special - values to get the trimmed results. )
Same for the column you are looking up.
Is $A$1 a value or the title? Start with the first lookup value.
Is there a pattern to what’s missing? The later letters in the alphabet?
Are you sure there are no blank lines in the middle? (End of table)
It counts from 1, so 6 gets you F column value.
You are searching column A, first column, sorted, I assume? The VLOOKUP must be for the first column in A1:Z15000
Finally, the error I kept making:
=VLOOKUP($A10912,[Pace_File_2.xls]Sheet1!$A$1:$Z$15000,6,FALSE)
I see you ran across this already, I used to forget the “$” in $A$1 so the lookup table was relative too when copied, A2:Z15001, A3:Z15002, A4: and so on. You have the correct formula…
Take the formula where it returns #NA and cahnge the final value in formula to “TRUE” - see where it thinks it is finding data, between which 2 rows in lookup table…
I just wanted to point out that the first column in the source table need not be in alphabetical order if the ‘FALSE’ option is being used.
Hmmm… you are right. I learned my one thing for today.
I tried this - Set A1 to 1, B1 to 1, C1 to “=(A1=B1)” it should say “TRUE”.
Do this with a pair of cells you think are equal from the two sheets - compare them and see if Excel tells you they are equal. If so, VLOOKUP should find them. If no, fiddle and see what is making them different.
In the category “Stupid but has to be asked”: (Meaning I’ve done it more than once 
)
You aren’t comparing “number as number” with “number as text”?