Performing an iterative lookup to return closest match

{ 5 Comments }

Suppose a worksheet name is Code_details.  The following values lie in range A2:A14 of this sheet:

A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123,
ASDFG1234567890 and ASDFG123456789

Suppose there is another worksheet named Summary where the following values lie in range B5:B7:

ASQ47809876,ASW675458 and QWERT

Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet.  If this exact value is not found there, then the lookup value should be trimmed by one digit/character from the extreme right i.e. the lookup value should now become ASQ4780987.  The search should carry on till an exact match is found.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

*