Using INDEX MATCH or LOOKUP - Microsoft Community

hi,

i have 2 books,(ca)&(cac-data) with column headers a (trial date), b (club), c (approver), d(designer), e (cat).

cac-data, cat is sequential number, running lowest highest.

trial date , club have matching values in both books.

book cac-data has values in columns c,d,e that want copied to ca,  c,d,e.

logic; if row in both books match values for trial date & club copy values cac-data c,d,e to same row & columns in ca.

right i'm using formula in ca, c2

=lookup(1,0/(($a$2='[cac.xlsx]cac-data'!$a$2:$a400)*($b$2='[cac.xlsx]cac-data'!$b$2:$b400)),'[cac.xlsx]cac-data'!c$2:c400)

for part works on rows wrong match.with the cat number not sequential.

or used twice, or not used. row of cac-data can used once , rows must used. cat number must sequential. 

i tried calculation using iferror tables , matched perfectly. if changed club name in cac-data still matched, shouldn't be.

=iferror(index(cac.xlsx!cac-data[@approver],match(cac.xlsx!cac-data[@[trial date]]&cac.xlsx!cac-data[@club],[@[trial date]]&[@club],0)),cac.xlsx!cac-data[@approver])

so i'm looking new  solution. perhaps index match approach.  i hope explanation satisfactory.

i have been looking accurate solution time. 

appreciate help. 

best regards, robert

with complexity easy break chain of events results want, , won't end user.

robert,

i understand problem, not change basic problem, there no simple compare algorythm out there can match pac = pacific agility clan (pac) = pacific agility clan

try yourself, there tons of short fuzzy compare routines out there:
https://www.google.com/search?q=fuzzy+vba

or have here:
https://en.wikipedia.org/wiki/jaro%e2%80%93winkler_distance

https://en.wikipedia.org/wiki/levenshtein_distance

imho simple , easy way go use conversion table, that's can say, sorry.

andreas.

Office / Excel / Windows 10