Need help with correct vlookup forumula in excel?

Question

I have two worksheets I'm working with, worksheet1 and worksheet2.

In both worksheets, I have a first name column and a last name column, in columns A and B respectively.

My goal is to take the customer account number from column C in worksheet1 and put into worksheet2 by matching the names between the two worksheets.

Here's the formula I'm using, but it's not working:

=VLOOKUP(A2:B20,worksheet1!A2:B20,3,FA...

I'm getting a #VALUE error.

Any help is appreciated.

Answer 1

vlookup can only lookup one value (one criteria)  so in your formula the first range (the criteria) cannot be a range of cells; it has to be a single cell.

generally what you'd use vlookup for is to find a particular match in a table, then return some result from that table. For example if you had first name, last name and acct # in A, B & C columns respectively.  Then (for example) in D1 you could type someones name (ex: Joe) and look it up and return Joe's acct #.  The syntax to do this would be

=VLOOKUP(D1, A1:C100, 3, FALSE).

since it seems like you're trying to match both names, vlookup won't work since it can only lookup one criteria (and that criteria has to be in the first column of your table).  

what I would do in your case is in Worksheet1, insert a new column in front of A (we'll call it "Full Name").  Then put in a formula of =C1&", "&B1.  So if B1 was "JOE" and C1 was "SMITH", now you have SMITH, JOE in A1.  Copy this formula all the way down.

now over in Worksheet2, do the same thing.  Then enter a vlookup of

=VLOOKUP(A1, WORKSHEET1!$A$1:$D$100, 4, FALSE). (assuming D100 is the end of the table).  This should look up A1 of worksheet2 in the table A1 thru D100 in Worksheet1 and return the 4th column (acct #).  Copy this all the way down as far as you need to.

note: the $A$1 reference...that tells excel to lock the cell reference and not change it as you copy the formula down.