How to Vlookup with partial Match?

[sreekhosh]

Hi,

Please see the below table and advise how to do the vlookup if the values are partially matching.



Regards
Sreekhosh

 

[Somendra Misra]

or alternatively this array formula:

=INDEX($C$3:$C$6,MATCH(TRUE,ISNUMBER(SEARCH(E3,$B$3:$B$6)),0))

Confirm with Ctrl+Shift+Enter


Edited: I had removed my earlier suggestion, as it was not valid for some cases.

Regards,

 

[Deepak]

If you are using xl>=2010 then could try this too.

http://www.microsoft.com/en-in/download/details.aspx?id=15011

 

[sreekhosh]

Guys thanks for your reply,

@Somendra: Great formula, but still not working in all cases Eg: Row No (3),(7).

@Deepak: Will install it and check. thanks


Sreekhosh

 

[Somendra Misra]

@sreekhosh

It's not working since punctuation in lookup value is not same as in lookup table.

It's seems your look up value will be approximately same but not part of the original data. Go through the below link: where a microsoft pluggin details are shared.It is the same, which is shared by @Deepak above. Also @shrivallabha had shared a link to get some UDF to do the same task from Mr.excel message board.

http://chandoo.org/forum/threads/fuzzy-lookup-add-in-for-excel.15724/#post-94929

Regards,

 

[sreekhosh]

Fuzzy Lookup is working fine for me. Thanks a ton for the wonderful information.

Sreekhosh