cant get vlookup to work

[smiler44]

I'm going mad, I cant get Vlookup to work, its returning the wrong value.


In cell D4 I have mark
in cell E4 I have =vlookup(D4,G3:h8,2,TRUE)

in cells G3 to G8 I have put these names one in each cell Mark Derek phil carl steve kevin

in cell h3 to h8 I have the name one in each cell john pete pete pete pete pete

the table is on the same sheet

cell E4 is giving #N/A

I have no idea what the problem is, can you help me.
If my table in G and H was on a different sheet what would the formula be?

does it matter if any cell is formatted to put the name in the middle or to one side?

thanks
smiler44

 

[scott-atkinson]

I have just tried to replicate this and the formula returns the correct value of 'john' have you checked to ensure that all words are formatted the same, ie.. TEXT or General? Vlookups can sometimes be affected by the formatting..

Just having text justification within a cell will not affect it

Have you also checked the text length to ensure there are no hidden spaces?

 

[smiler44]

thank you Scott. There were two things wrong. As you say hidden spaces and the names were not in alphabetical order.

I emailed my spread sheet to someone and they found the hidden spaces.

thank you for helping

smiler44

 

[scott-atkinson]

thank you Scott. There were two things wrong. As you say hidden spaces and the names were not in alphabetical order.

I emailed my spread sheet to someone and they found the hidden spaces.

thank you for helping

smiler44

The Alphabetical aspect would not affect the Vlookup unless of course the source and target names were not spelt the same.

Instead of using a Vlookup, I tend to use Index and Match functions, they are far easier to work with if you have a large number of Vlookups that you need to use.

 

[namliam]

Scott, the TRUE in the last variable of the function makes it so that order DOES matter.....
If you want order to not matter you need to make it FALSE.