Forum Discussion

venkatvedavyasan's avatar
venkatvedavyasan
Copper Contributor
Mar 14, 2024

Need a formula to get the vlookup with multiple similar value

 

Hi,

 

I have a list of people with full name and location. In the sheet I received I have only the last name and Location.

 

What I want is to find the full Name with help of the last name and location.

 

The issue I'm facing is that I have same Last name for multiple persons.

 

I'm trying to use VLOOKUP but the it is returning wrong values.

 

Data that I have with Full Name and Location

 

Data I got with Last Name and Location

 

 

Formulas and Functions

2 Replies

Sort By
  • MAngosto's avatar
    MAngosto
    Iron Contributor
    Mar 14, 2024

    venkatvedavyasan 

     

    Hello there!

     

    Based on your definition of the problem, I assume two possibilities. Either you are just trying to find the names and locations in order to see the full names or you just need to find an extra information of them (say, their salaries, which are in an adjacent column).

     

    If you are just trying to find the location of the full names of these people:

     

    • Assuming G1:G11 is where the Last names is located.
    • Assuming A1:A11 is the range where you have the list of Full names.
    • Assuming B1:B11 is the range where you have the list of locations (adjacent to full names).
    • Assuming H1:H11 is the range where you have the list of locations (adjacent to last names).

    Procedure:

    • Create a list of ascending numbers ranging from your first row with names to the last one. Say, you put C1 = 1, C2 = 2, C3 = 3, and so on until C11.
    • Use a MATCH function (in any blank cell you prefer) to retrieve the location (specific number that you have created just before) of that specific person you are trying to find.

    Try:

     

     

    =INDEX(C1:C11,MATCH(1,(A1:A11="*"&G1&"*")*(B1:B11=H1),0))

     

     

    This would return a number, say 4. This would mean that, given that last name you selected and the location you selected, the functions goes and looks for which of the rows fulfill the condition of containing that last name and that location at the same time. Then, return the value 4, indicating that that is the location of the row where this happens. You would then go to range C1:C11 and look where 4 is located, and you would directly know that is the person you are looking for.

     

    If you would like to find extra information of that person (say, salary):

     

    You would not need to create the C1:C11 numbered range and just select the range where this extra information is located. The same formula will return the information (slary, for instance) directly.

     

     

    I hope this works well. I am quite a beginner so I feel there would be a much efficient way to do this. I am open to additional suggestions to my solution or even a completely new one.

     

    • MAngosto's avatar
      MAngosto
      Iron Contributor
      Mar 14, 2024

      Hey there,

       

      Intending to simulate this scenario, kept giving me an error. I think I found out that the inside match function with more than one conditions does not accept wildcards. Tried to play a little bit and found a little trick to overcome the error.

       

      It is basically incorporating a if+match function inside the first match function condition. If the match is met, it returns a simple logical formula such as 1=1, serving as the condition of the outside/main match function.

       

      It looks as follows:

       

      =INDEX(C1:C11;MATCH(1;(IF(MATCH("*"&G1&"*";A1:A11;0);1=1;""))*(B1:B11=H1);0))

       

      The values follow the explanation mentioned in my previous reply. 

       

      Would anyone be willing to comment on this idea? I know there might be tens of more efficient options, but as a beginner I was trying to play a little bit with this one.

       

      Thanks!

Resources