I am assuming you have a file H:\800326_Prev.xls. Yes? Try dropping the single quotes. You have them in the wrong place, and don’t need them here anyway.
The FALSE means “Find an exact match in the lookup.” TRUE would mean “Look for the closest match in a sorted list.” If you specify FALSE and the value is not found, then the result will be #NA!. If you specify TRUE and the value is not found, then the result will be just some value.
You really need to read the Help page on VLOOKUP.
Yes. OK, I’ve replaced it with this: =VLOOKUP($A1,H:\800326_Prev Sheet1!$A$1:$D$22,2,FALSE) First, it opens a box and I have to choose the file. Strange, since I told it the file in the code. Then when I double-click the file it returns #NAME?
So it will find a match, even if the matches are separated by one or more rows?
I did. It’s not helpful.
You removed the quotes, but you also removed the square brackets. The quotes are used if ther are embedded spaces in the sheet name. The square brackets indicate that the range is in a different file–you have to put those back.
I don’t understand the question.
The VLOOKUP function takes a value (the first argument), then looks in another range (the second argument) to see if the value exists in the first column of that range. (It just finds the first match.) It returns the value in the same row as the match, according to the column specified in the third argument. So if the third argument is 2, it will find a match in the first column, then return the value from the second column in the same row where it found the match.
If the fourth (optional) argument is TRUE, then the list must be sorted. It will look for the match, but if it realizes that the match isn’t there, it will return the next value in the sort order after what you’re looking for (if you look for CAR, and the list has CAP, CAT, it will match CAT).
If you instead specify FALSE, the list does not have to be sorted, and it will require an exact match. If there is no exact match it will return an error.
Kind of hard to explain without sitting with you. Hope this helps.
Let’s see if we can go step by step. (Note I’m looking at a different file now.)
=VLOOKUP($A1,’[H:\833983_Prev.xls]Sheet1’!$A$1:$D$22,2,FALSE)
=VLOOKUP($A1, says 'This cell will look up the value in A1.
’[H:\833983_Prev.xls]Sheet1’ is the file I want to look in.
!$A$1:$D$22,2,. Something to do with columns.
OK. Since Sheet 1 isn’t the name of the sheet, I put in this:
=VLOOKUP($A1,’[H:\833983_Prev.xls]833983 Cust List’!$A$1:$D$22,2,FALSE)
(If I leave it Sheet 1 it returns #N/A.) Now when I click on the cell, the formula has changed:
=VLOOKUP($A1,’[H:[833983_Prev.xls]Sheet1]833983_Prev.xls]Sheet1’!$A$1:$D$22,2,FALSE)
Weird, eh? Even I can see the brackets don’t match. But it changed by itself anyway.
As I said, these ‘power user’ things are not used here. The formula is not in the form of a structured program like COBOL or Easytrieve so it’s difficult to see what they’re doing. And then the formula re-writes itself when you try to use it…
Step by step:
=VLOOKUP($A1,'[H:\833983_Prev.xls]Sheet1'!$A$1:$D$22,2,FALSE)
=VLOOKUP
the name of the function
$A1
The location of the value that the function is going to look
up. The $ means absolute column: If you copy the
formula to any other column, the column will still be $A.
[H:\833983_Prev.xls]
Filename containing the data where you are going to try
to find the value. (If the data is in the same file, you
don’t need this.)
Sheet1
The worksheet name in the file containing the data
where you are going to try to find the value. (If the
data is in the same worksheet, then you don’t need this.)
The ! separates the sheet reference from the range. The
single quote encloses the [pathname]sheet combo if there
is an embedded space in the sheet name.
$A$1:$D$22
This is the range that you will search for the value.
A /range/ is a set of cells. In this case, it is a contiguous
set of cells with A1 as the upper left cell and D22 as
the lower right cell. It will search only in the first column
of this range. As before, the $ means an absolute
reference. Since all of the references in this expression
are absolute, you can copy the formula to any other
cell and it will always refer to the same range.
2
The relative column of the value to return. The value
will be returned from the second column of the target
range, column B in this case.
FALSE
Hopefully I explained this sufficiently earlier.
That is very strange, I have no idea why it would have changed. I have never seen Excel change a formula unless there is an error in it, in which case it asks your permission first.
Using Excel formulas is a different head trip than procedural coding. Using basic functions is not really considered power user stuff (power users are into outlining, pivot tables, and macros).
If all else fails feel free to click on my user name and send me an email, attaching your two files.
Thanks for the explanation. It just doesn’t like me!
I’ve zipped a couple of files and sent them your way.
Many thanks.
CookingWithGas: Thank you for the email! The formula is working as long as I don’t put a Drive name in it.
Yay, I’m glad you got it figured out! This just made me realize that it is REALLY hard to describe a vlookup on paper - I think it would be MUCH easier with two people looking at the same thing in person. Yowza.
For anyone interested, the same files and the explanation I sent to Johnny L.A. can be found in a zip file here (properly sent as binary :o this time)
It’s hard when you seem to be the one in the office who knows most about Excel. As you can probably tell, I write structured programs. There was a disconnection between my ‘Do this, then do this, then if this, then…’ and the way formulas are written in Excel until CookingWithGas sent me incredibly detailed emails. At least I can do simple formulas (simpler than VLOOKUP! 
). But in the office…
This morning I showed my boss that she can select a range of cells by using click-shift+click. We have a couple of files that require two or more rows of numbers to be added up. Up until last year my boss had been using a calculator and typing in the results, and that’s how she showed my coworker to do it. I showed them how to use the Sigma (Sum) key, how to drag the formula across the row to apply it to all of the columns, and to paste values into the top of the block before deleting the other rows.
So there’s no one I can ask in the office when something like this comes up. My coworker was amazed and impressed that there are people like the lot of you whom I could ask. (Today she asked me how I knew Excel could do this at all. I told her I didn’t know, but I was pretty sure there must be a way to do it.)
Thanks again.
And CookingWithGas: I’ll try that other thing you sent in the morning.
That’s one for Dilbert. Seriously, you should send that to Scott Adams. Anyone who would type numbers into Excel then use a calculator to add them up is just…not very smart. That just blows my mind.
I work for a non-profit. The president started there when I was starting kindergarten. Of the 18 employees, I’d say half of them have been there for a quarter of a century. The equipment is… not state-of-the-art, let alone cutting edge. Even the furniture is worn out. But I really like the job. It’s pretty casual, the people are nice, the office is in a funky-cool part of town, and I get to telecommute a couple of days a week. When my boss started there (25 years ago) they did things with paper and fax. We don’t have Goldmine or another system to keep electronic notes; our files are manila folders. I think it’s a case of TTWWADI. I’m just happy that even though I’m by no means an expert I can still give a few pointers. I’d hate to poke fun.
Basically I’m lazy. Why should I work hard when a computer can do it for me? So when I come up against repetitive tasks (like this one), I ask questions and try to make it easier. With Easytrieve I can reformat the files into the format they need to be. (Not all of them, as there are too many and I’m the only one who knows how to use Easytrieve. I have offered to teach people.) And there’s a new, more complicated format for new data. So rather than someone entering it manually, I turn the files into text and then it takes about a minute to process them. I don’t know if I’ll get my boss to use the VLOOKUP formula, but I’m sure my nearing-60-years-old coworker will. She takes copious notes whenever I show her how to do something. (I’m working on a step-by-step document on how to use VLOOKUP for our specific needs.)
CookingWithGas, I came across something weird this afternoon when I used the corrected formula you sent me offline. I opened a file and inserted three columns. I pasted the formula into the first cell of one of them and modified the file and sheet names. The previous file is on a different drive. Then I dragged it across the other two columns and modified them. So far, so good. Then I copied the three formulas. I selected the next three cells below, shift+clicked on the las cell in the third new column, and pasted formula.
It just sat there. It’s like Excel totally locked up. I’d done the same thing earlier on a different file and I did notice that it took a long time to update. But this file just wouldn’t finish. I checked email, browsed eBay, poked around on the 'Net, and still it was on the old hourglass. I ended it in Task Manager. Tried it three times with the same results. I’m telecommuting tomorrow, so I’ll save the files to my Mac and try it there. But why did my copy-and-paste lock up Excel on the PC? There are 6,000+ lines, but I thought it should still work. (I have an 80,000-line file – split between two files since Excel can’t load more than 65k lines – coming up. I need to clean it up manually this time, but will do a match-and-fix next time. If I can’t get VLOOKUP to not lock up the programme I can always use Easytrieve.
The best way for me to diagnose this is if I have the same files you are working with in the state they are right before your last step of copying the formulas.
Meantime I’ll see if I can reproduce it with my own files.