Cannot get vlookup to work

[benneesham]

Hello all,

I am trying to get a vlookup to work and it is stubbornly refusing!
Any help as to what ive missed would be much appreciated.

I want to bring back data from worksheet 2 and return it to worksheet 1 in the same workbook.
The search and refernce values exist in both worksheets in the first column (A) on each worksheet.
The data I want returned is in column B on worksheet 2.

I am entering the lookup formula in a cell in column B in worksheet 1, referencing worksheet 2 column 1 and supposed to be returning column 2.

here is example syntax:
=VLOOKUP(A7119,Data2!A:B,2,FALSE)

Keeps returning #N/A errors

The marquee box selecting thingy-majig used for selecting cells/ ranges is different to usual as well. the flashing lines are smaller and less noticeable.
Is this a clue?

Both sheets are formatted as general and were originally .csv files uploaded to Access DB and now exported to Excel.
there were some extra characters in the values which (I hope) I have removed with Trim and Clean.

I have tried with different formatting, saved changes, changed columns about and loads of other things. Help.

Sample attached.

Regards

Benn

 

[Lukn4it]

Vlookup vs. Tools > Lookup option

Have you ever tried using the lookup option under tools? This might be a good option concidering it writes the formula for you.

 

[benneesham]

Hi there,

Yes, I have tried using the formula wizard, again to no avail.

Regards

Benn

 

[Lukn4it]

I opened your sample that you sent and on the sheet 2 columb A all needs to be converted to numbers, you do this by manually selecting all the cells and then selecting the little popup that contains selections one of which is "convert to number" once I did this the error message was gone and it returned a value.

 

[benneesham]

Hello again

I tried formatting both column A's cells as Number this morning and it did not work, is that similar?

Just tried formatting as number again now and no luck with formula.

I have also selected the cells in column A sheet 2 and do not get a little pop up with 'convert to number' option. (no pop up at all).

Is there another way to get to this?
I have tried Paste Special to get just values into cells formatted for numbers.

Regards

Benn

 

[unmarkedhelicopter]

It's Data2 col A that are NOT numbers, try creating another column that =A2+0, drag down, then copy paste values into A again, delete temp column.

 

[benneesham]

Hi

I have tried creating new column and copying and paste special values into the new column (formatted for numbers), deleting old column and typing formula again.

Still no joy.

Regards

Benn

 

[unmarkedhelicopter]

You didn't try very hard did you ?
I did exactly as I suggested earlier and got this.

 

[benneesham]

No need to be rude now...
How do you know I have not been trying hard?
I have been working at it for days with the understanding I have of excel and searching the net to learn more.
I have it working now anyway thank you.
A friend from finance helped on it this morning as well and moved it all to a new workbook, formatted and voila!

 

[Brianwarnock]

Benn I think UMH 's response was born out of frustration, you ignored both his and Luken4it's information, neither said reformat the cells or that you could do it by formatting, you have to CONVERT the text to numbers, Read both posts again, and also click on the "help with this error" in the drop down mentioned by Lukn4it.

Brian

 

[DanG]

I little trick I learned on converting is to select the column and use the text to column feature. That works when changing data types doesn't and is very easy.

HTH

 

[speakers_86]

Heres how I did it, highlight all the numbers in row a on sheet 2, then click on the exclamation point that appears on the spreadsheet, click convert to numbers.

Hope it helps.

Attachment below.

 

[Brianwarnock]

Heres how I did it, highlight all the numbers in row a on sheet 2, then click on the exclamation point that appears on the spreadsheet, click convert to numbers.

Hope it helps.

Attachment below.

Which is one of the options it mentions in help, unfortunately too many people wont make the effort to read help.

Brian