Hello,
I'll throw this out to see if anyone has seen this. I didn't find anything like it when searching the forum. Thanks in advance.
I'm trying to extract a value from a string in a cell and use it as the basis for a VLOOKUP on a different sheet.
For example:
Cell D63 on Sheet 1 contains "64500 - 54488 - Custom work"
Sheet2 A2 thru B300 contains the lookup table of custom projects and project owners
I need to be able to extract the "54488" from the string in D63 and use it as the lookup from Sheet2 and return the corresponding custom project owner.
I've been able to extract the "54488" using =SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","")
I've been able to use =IFERROR(VLOOKUP(0+LEFT(D60,FIND(" ",D60,1)-1),Sheet2!$A$2:$B$300,2,FALSE),"") to lookup the project owner when the project number, 54488, is the first value in the string. But my input data is changing and the 54488 is no preceded and followed by " - ".....space dash space.
When I tried using SUBSTITUTE(MID(SUBSTITUTE(" - " & D63&REPT(" ",2)," - ",REPT(",",255)),2*255,255),",","") as the first parameter for the VLOOKUP, I get #N/A. I've confirmed that 54488 is in the table with a corresponding project owner.
Any ideas?
Bookmarks