VLOOKUP formula - match two columns (partial) and return value

[Tom_Khachatryan]

Hello there. 
I am facing an issue trying to match two columns and return value to third with the Vlookup formula. 
The idea is to do an approximate name match and get the correct value. 

Will appreciate your help. 
Thank you

[#2 · Accepted solution]

@Tom_Khachatryan 

Does this work for you?

=VLOOKUP(LEFT(C8,FIND("^",SUBSTITUTE(C8," ","^",3))-1)&"*",$J$3:$K$29,2,0)

[#3]

@Subodh_Tiwari_sktneer, it is superb. Thank you. 

[#4]

You're welcome @Tom_Khachatryan! Glad it worked as desired.

[#5]

Hello @Subodh_Tiwari_sktneer , I tried the same formula in another sheet, but still getting the same error. I can not understand the core of this issue. 

[#6]

Please pay attention to the lookup value in the formula, the values in lookup_column in the table_array must start with the lookup value.

[#7]

In your Vlookup formula, you are using 0 as the last argument which is used for an exact match. Use 1 instead as you want to do an approximate match.