vlookup

[danielroberts560]

I have a list of names and amounts on one spreadsheet and a list the same names on another. all I want to do is a vlookup to get the values from one spreadsheet on to the other next to the correct name. Now i know all the names are on both spreadsheets but it just doesn't work. If I copy the name from the vlookup sheet on to the other it finds it. Is it possible that the format of the cells are different and thats why it wont pick it up

 

[Yard]

Do you have some secret spaces in any of your values?

 

[justme]

=VLOOKUP(A26,'2009'!A2:B25,2,FALSE)

Does your formula include a the name of the other worksheet? In this instance the sheet name is 2009.

 

[danielroberts560]

I can't see any hidden spaces. Yes the formula does include the name of the workbook; just workbook 2

 

[justme]

Are you working with two worksheets in the same workbook, or are you working with two separate workbooks?

 

[danielroberts560]

i tried both. it didn't work

 

[Yard]

OK, so it must be something within the values.

Take one of the values that you believe to be the same in both lists. Test if they are equal by using

=EXACT(A1,B1)

where A1 and B1 refer to the cells containing the values. I'm expecting it to return FALSE. Then we can start to figure out how they are different.

BTW, when you say "it didn't work"...what do you mean? Do you get an error or do you get an unexpected value returned? What is the exact formula you are using?

 

[danielroberts560]

I got an error message.

I have just tried the exact formula and apparently they are not the same!!! I cannot see why!!!!

 

[Yard]

OK - but WHICH error message?

Try performing the same test but using:

=EXACT(TRIM(A1),TRIM(B1))

If that returns TRUE then one of your values has a space in it somewhere.

If it returns FALSE, try

=EXACT(CLEAN(A1),CLEAN(B1))

If that returns TRUE then you have some non-space non-printable characters in your text.

Whichever is the outcome, you then have the decision to "clean" your data, or to add either TRIM or CLEAN into your VLOOKUP function.