Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

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

Copper Contributor

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

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.

Thank you

6 Replies
best response confirmed by Tom_Khachatryan (Copper Contributor)
Solution

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

Does this work for you?

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

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

@Subodh_Tiwari_sktneer, it is superb. Thank you.

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

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

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

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.

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

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.

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

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.
1 best response

Accepted Solutions
best response confirmed by Tom_Khachatryan (Copper Contributor)
Solution

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

Does this work for you?

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