Originally Posted by
Hans Gatu
But, as you mentioned, it work best with no common values in the M and N colums.
However, in the real workbook there are common values, so it doesn't work with these values.
In this case it's not the similar values in the different columns, it's because your strings include the tilde(~) which is a wildcard character (actually it's the escape character when you want to look for a literal asterisk or question mark).
So a tweak to the macro in the attached:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D5")) Is Nothing Then
Application.EnableEvents = False
Set ddd = [IF(D5="M",M3:M12,N3:N11)] 'this is the data validation formula of cell D7
SearchItem = Replace(Range("D7").Value, "~", "~~", 1, , vbBinaryCompare)
x = Application.Match(SearchItem, Range("M3:M11"), 0)
If IsError(x) Then x = Application.Match(SearchItem, Range("N3:N11"), 0)
If Not IsError(x) Then Range("D7").Value = Application.Index(ddd, x)
Application.EnableEvents = True
End If
End Sub
In this case, even if there are similar values in columns M/N, as long as they're in the same row (which they are) it'll be fine. If ever that won't be the case, then the macro can be altered to cater for that, it's just that it'll be a bit more long-winded.
I've ignored the cells M12:N12.