In this post we will see how could we vlookup for first word in a string, here string could be of any length and/or type.
The reason I am explaining this post is because sometimes we get data which are proper text strings and are sometimes inconsistent in their naming conventions.
They may have correct first and/or second word and then we have some discrepancies as we proceed further in the string and our base data file which we are going to do vlookup from ,will have every character correctly stated.
So in those kind of scenarios, we are required to do vlookup for the first word in excel.
Below is the sample data and our reference table that does have some discrepancies and we will do vlookup on the basis of first word in excel.
In the above data, we want to vlookup the price on the basis of first word as you could see that there are mismatch in the entire name between the actual data for which we will vlookup and the reference data which has the price for every items.
So in this case we will use simple LEFT and FIND formula to extract the first word from the string and then will do the lookup on the basis of first word.
Step 1
Enter the formula =VLOOKUP(LEFT(A3, FIND(” “, A3) -1),$D$3:$E$6,2,FALSE) in cell B2 as shown below in the pic.
As you could see the first argument to VLOOKUP formula is LEFT(A3, FIND(” “, A3) -1) which is extracting the first word from the string with the help of FIND function.
Step 2
Now drag the formula till row six to accommodate all of your data and you could see we have done the vlookup with first word in excel.
Same logic could be applied to different requirements, if you wanted to VLOOKUP on two words, then just extract two words and separate them with the delimiter that you have in the reference dataset.
Hope this helped.
