Forum Discussion
Need a formula to get the vlookup with multiple similar value
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.
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!