Vlookup formula is working on some criteria but not others

[3smees23]

Hi,

Me again with this age old problem. The vlookup formula is only partly working on the attached sheet, but I cant find an explanation as to why it can look up some criteria but not others.

I have to updated these prices on a monthly basis

Thanks in advance for your time

Sam

[Andrew-Mark]

Upon doing a simple find search on your items in Col C on Sheet1, they are not in Col A on Sheet2.

[mrice]

Greetings from just down the road in Frimley.

You appear to have a data quality issue here. VLOOKUP is looking for an exact match and is not detecting the similarity between codes on the two sheets which differ in subtle ways (e.g. in row 17 contains a hyphen while the code on the second sheet doesn't).

You could use a variation on the formula such as ..

=VLOOKUP(SUBSTITUTE(C17,"-",""),Sheet2!A:D,4,0)

.. in this case but you appear to have quite a wide variety of variations between the codes.

I tried using the following macro to clean up the codes on the two sheets by removing any non alphanumerics and converting all to upper case.

Sub Test()
For Each Cell In Selection
    For N = Len(Cell) To 1 Step -1
        Select Case Asc(Mid(Cell, N, 1))
            Case 65 To 90, 48 To 57
            
            Case 97 To 122
                Cell.Value = Left(Cell.Value, N - 1) & UCase(Mid(Cell, N, 1)) & Right(Cell.Value, Len(Cell.Value) - N)
            Case Else
                Cell.Value = Left(Cell.Value, N - 1) & Right(Cell.Value, Len(Cell.Value) - N)
        End Select
    Next N
Next Cell
End Sub

This certainly helps but there are still a significant proportion where there isn't an exact match which will need some manual inspection. Please visit my home page if you want some local support on this.

[3smees23]

Thanks Martin

What i might do is change all the manufacturer codes into the supplier code (numeric code in column B). then I wont have to check any N/A#'s

cheers again for your time!!!

Sam