VLOOKUP with Sheet2

[Robyne]

I can't for the life of me get this to work. I have Sheet1 that looks like this:

ColumnA ColumnB
AcctNbr CustomerName

And Sheet2 that looks like this:

ColumnA ColumnB ColumnC ColumnD ColumnE
AcctNbr CustName PrinBal IntRate DueDate

and I need to bring the data on Sheet2 onto Sheet1. Can anyone help? Thanks!

 

[GlennUK]

Is AccNbr unique enough to retrieve data on?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Yes, use VLOOKUP, using AccNbr

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Robyne]

On Sheet1, yes they're unique.

 

[mcongdon]

Are they also unique on sheet 2? Meaning 1 AcctNbr on sheet 1 matches 1 Row in sheet 2.

 

[Robyne]

Yes, there's a 1-1 match between Sheet1 and Sheet2.

 

[SkipVought]

Have you tried VLOOKUP? It will work for you.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Robyne]

I have tried VLOOKUP, but I can't make it work. It looks like I left that part out of my post, sorry. I toyed with it most of yesterday. If I get a correct syntax, the data doesn't come forward. Mostly, I had syntax errors.

 

[SkipVought]

Please post your formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Robyne]

I think it's really close.

=VLOOKUP(Sheet1!A2,Sheet2!$A2:$A2428,=Column(C2), FALSE)

But this returns the Account Number instead of what's in Sheet2, ColumnC.


If I udnerstand the formula correctly, this says look at Sheet1, Cell A2 and match that value with an entry in Sheet2, Cell A2 through A2428, and enter what's in Column C. It doesn't appear to like the Sheet1 part. Thanks for your help!

 

[Gavona]

=VLOOKUP(Sheet1!A2,Sheet2!$A2:$B2428,2, FALSE)

Vlookup always looks for a match in the first column of the lookup range and (if match found) returns the value in the nth column of the lookup range - in this case the second column.

Did you try using the formula wizard to help construct the formula? Or using Help?

Gavin

 

[Robyne]

Yes, and 2 Excel manuals. Obviously, I didn't get very far! And in looking at posts on the internet, VLOOKUP may not give me my desired outcome. According to one post, the columns I'm matching have to be in the same order - and they may not be.

 

[SkipVought]

I rarely use VLOOKUP. This is because 1) I use Named single column, COLUMN ranges and 2) my lookup range is seldom in the left-most column.

Consequently, I use INDEX and MATCH almost exclusively.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

The columns do not have to be in any particular order if the final argument is False. In this case vlookup will work down the first column in the range you have specified until it finds an exact match and will return a value from that row in the range in the column indicated by the third parameter.

The data you posted does not appear to illustrate the issues that Skip's approach solves.

Did the formula I posted work? If not:
1. Are you certain you have an exact match? To test this try copying the Account number from A2 to the lookup range - this can eliminate issues where there appears to be a match but in fact there are extra spaces etc etc.
2. please post the formula you used.
3. What error did it return?


Gavin