Vlookup Text LEFT of Semicolon

[jski21]

Happy Holidays Team,

Can't seem to determine why this formula isn't working. I'm attempting to lookup all text to the left of a semicolon and return a value. Value is #N/A:

=VLOOKUP(LEFT(Q4,FIND(":",Q4,1)-1),Q4:S4,3,FALSE)

Section 4.xlsx
	Q	R	S
1			#N/A
2
3
4	Transformation Initiative:		200,000
4-1

Cell Formulas
Range	Formula
S1	S1	=VLOOKUP(LEFT(Q4,FIND(":",Q4,1)-1),Q4:S4,3,FALSE)

Thanks for the second set of eyes and your help.


jski

 

[RoryA]

You're stripping the colon off Q4 then trying to match it to Q4, which has a colon, so it doesn't match. Not really sure what you are really doing, but perhaps you need a wildcard:

=VLOOKUP(LEFT(Q4,FIND(":",Q4,1)-1)&"*",Q4:S4,3,FALSE)

 

[jski21]

You're stripping the colon off Q4 then trying to match it to Q4, which has a colon, so it doesn't match. Not really sure what you are really doing, but perhaps you need a wildcard:

=VLOOKUP(LEFT(Q4,FIND(":",Q4,1)-1)&"*",Q4:S4,3,FALSE)

Bingo RoryA. It's been a 'wild' day. Thanks. Merry Christmas!

 

[RoryA]

Merry Christmas to you too!