Excel Vlookup text 3

[IngDep]

Have a workbook with two worksheets. One worksheet has data similar to;

Name----------Rank
Smith, Bob----1
Jones, Jill---2
Wall, Kim-----3

The other worksheet has data similar to;

Name------------Address------------Phone----Rank
Stat, Sheila----111 Ave------------214009
Smith, Bob------245 City Drive-----324568
Jones, Jill-----324 Sand Drive-----153897
Objective is to use vlookup to populate the "Rank" column on the second worksheet. Also, could I also perform this sort of vlookup when the data is in separate workbooks?

Insight greatly appreciated.

 

[petrosky]

Hi there,

If your first list of rankings is on Sheet 1,

You could place this in your rank cells.

=VLOOKUP(A2,Sheet1!A:B,2,FALSE)

For VLOOKUPs to other workbooks...use this syntax.

[yourworkbook.xls]yourworksheet!$A$6:$C$11

Hope this helps,

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[IngDep]

Will the vlookup formula provided work with text?

 

[petrosky]

Hi there,

It works for me.
The syntax for the other closed workbook is more like the following

=VLOOKUP(A2,'C:\[Book1.xls]Sheet1'!$A$1:$B$2,2,FALSE)

Regards,

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[IngDep]

Was able to finally receive a successful vlookup by using the following formula;

=Vlookup(TEXT(B2,"0"),Rank,FALSE)

However, I only receive a value for just two rows - only when I copy and paste from worksheet 1 to worksheet 2 the actual values in column A. In other words, guaranteeing that the person name is the same on both worksheets.

It appears that the use of the Data/Text To Column procedure
that I used initially is causing the issue whereby all of values in the Rank column on worksheet 2 is N/A except those instances where I used copy/paste...

 

[Gavona]

You need to carefully examine and compare the names in the two worksheets to identify how they differ. Possibly you have some leading/trailing spaces in one set. If you can't see what is going on by editing the cell values then:

Paste the value from one sheet into cell A1
Paste the similar value from the other sheet in B1
In D1 type =A1=B1 we expect this to return false as the lookup is failing
In E1 type =trim(A1)=trim(B1) does this work?
If not try using the clean function

If still no joy then you could step through comparing each character individually

In cell C2 to C99 enter 1, 2, 3 etc
Formula for A2 to return the character code
=code(mid(A$1,$C2,1))
Copy this to B2
In D2 enter =A2=B2
Copy these three formulae down.

To display the character rather than the character code use the Char() function. Note however that several character codes can display as "spaces" which is why I suggest using the Code function to find the differences.

Finally, when you have identified the problem either fine tune the way you used Text to Columns to eliminate it or use edit,Replace or the Substitute(), Clean, or Trim functions to clean the data up before using the vlookup.



Gavin

 

[SkipVought]

[tt]
=Vlookup(TEXT(B2,"0"),Rank,FALSE)
[/tt]
will not work. Peter already gave you the VLOOKUP formula that WILL work in sheet2

"Also, could I also perform this sort of vlookup when the data is in separate workbooks?"

Yes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}