Vlookup using wildcard for fetching the data

  • Hi All,


    I stuck while working on Vlookup with wildcard thing. Actually i have two column, one is "Name" and second is "Amt". In "Name" column i mentioned "Harindra Gopal Devadiga" and in "Amt" column i mentioned "100".


    Same way i have another two column with same names, "Name" and "Amt", now in "Name" column i mentioned "Harindra Devadiga" i skipped Gopal from it. Now what i want is with the help of VLOOKUP with WILDCARD i have to bring that same Amt.


    For reference i attached the file in which the red color cell have a formula "=VLOOKUP("*"&D2&"*",A2:B2,2,FALSE)". Can anyone help me out in this.

  • Re: Vlookup using wildcard for fetching the data


    Can you not just set the false to true in order to get approximate matches?


    Although this wouldn't be fool proof and may give false results based on how your reference data is sorted.

  • Re: Vlookup using wildcard for fetching the data


    If the data is as consistent as in the sample file, then you can use:


    =VLOOKUP("*"&SUBSTITUTE(D2," ","*")&"*",$A$2:$B$4,2,FALSE)


    which will try and match each word in D2 (in the same order) with column A.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why