I'm trying to use a vlookup to pull information from one sheet to another but the reference information is different. For this example I put it on one sheet to make it easier. The information is pulled from 2 different systems. I want to search Column A for the information from Column D and return the corresponding mileage in Column E. Originally I wrote a formula for the partial string I was looking for, =LEFT(D3,SEARCH(" ",D3,SEARCH(" ",D3)+1)), but only returns #N/A when entered as vlookup, =VLOOKUP(LEFT(D3,SEARCH(" ",D3,SEARCH(" ",D3)+1)),$A$2:$B$18,2,0). I also tried using * wildcard, =VLOOKUP("*"&(LEFT(D3,SEARCH(" ",D3,SEARCH(" ",D3)+1))),$A$2:$B$18,2,0) but it still returns #N/A.
Any help would be greatly appreciated.