VLookup is behaving Strangely

[shaunk]

I have a four column spreadsheet. I have a Vlookup function which takes the value in column D, searches for it in column A and returns the value in the adjacent column B, and places it in column C.

The formula in cell C243 is :
=VLOOKUP(D243,A:A:B:B,2,FALSE)
Contents of cell D243 is "CN Podiatry/ Footcare Service Greater Newcastle Cluster*PODIATRIST * * PODIATRY - GENERAL - INPATIENT * HOSPITAL* FACE TO FACE"

The formula in cell C245 is :
=VLOOKUP(D245,A:A:B:B,2,FALSE)
The contents of cell D245 is "CN Podiatry/ Footcare Service Greater Newcastle Cluster*PODIATRIST * * PODIATRY - GENERAL * HOSPITAL* FACE TO FACE"

For these particular search strings in Column D, which are definitely different, they are returning the same value from Column B. Column B definitely does not have duplicates.

The search strings are long and complicated, but are definitely different.

Thanks
Shaun




The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[GlennUK]

That's because you have "*" in your data, which is a wildcard character. Change the contents of C243 and C245 to have "?" instead of "*".

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

Oh, and an alternative is to change your formula to be:

=VLOOKUP(SUBSTITUTE(D243,"*","?"),A:B,2,FALSE)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[shaunk]

Thanks for that. Worked beautifully.

A footnote is that using Ctrlf+F to find the second string (D245) in column A also found multiple occurances when it should have found only one. So the * causes problems generally.

The risk with keeping an open mind is having your brains fall out.
Shaunk

 

[GlennUK]

A way to use Ctrl-F to find strings with * embedded is to specify ~* instead of the * within the string. The "~" tells Excel to treat the following character as a string instead of a wildcard.

Cheers, Glenn.

Beauty is in the eye of the beerholder.