Search Database

I have an excel database where I have customers and numbers like this>>>

CustomerName:JobNumber or sometimes CustomerName:JobName:JobNumber

example Smith, John:123 or Company Corp:Brown, James:124

I need a VLOOKUP (or something else) that can search for a number right in that database, without any modifications to it

I found a way to separate JobNumber from the rest...
=RIGHT(B34,LEN(B34)-FIND("^^",SUBSTITUTE(B34,":","^^",LEN(B34)
-LEN(SUBSTITUTE(B34,":","")))))

but that requires me to modify the database

example of what I need,
(Sheet2)
B1=Smith, John:123 C1=$789

(Sheet1)
A1=123

=VLOOKUP(A1,#CODE#) -- or something else
yields $789

that just returns the value of C1, or which ever the first row u choose