Returning cell value that contain specific text

[rosto]

Hello,

I need a formula that returns the value of the cell, from range, that contains a specific text.
I attached a jpg to be more specific. Another formula gives me the result "2" and i need a formula to find in column A the cell that contains "2" and return the value of that cell. The result of the first formula is not possible to be found twice in column A.
Thanks.

[Alex1]

Can you explain how you want the result displayed if more than one match is found?
In general a screenshot doesn't help much because we cannot see your formula and settings in use.

[rosto]

Sorry, perhaps I wasn't clear enough. If you read what i said in first post, you'll see that result of first formula can not be found twice (or more) in column A, so there can not be multiple results.
If you want, i attached now a simplified example.

[RoryOF]

Will the value to be found always be the last digit in the string to be searched? Will the strings cease at a9, or will they have vales such as a12or a22?

[rosto]

In principle the first formula returns 3 letters text, so in column A can be 012, or 022, or fr2 but the first formula result will be in that form (012, 022, fr2).
Yes, the result of the first formula are the last 3 letters in column A list

[Alex1]

If you isolate the numbers and copy them to a column inserted to the left of A, you can use the VLOOKUP function.

[RusselB]

If I understand correctly, you want to search through column A for a partial match where the matching portion is in Sheet1.A1
Check sheet3 of this attachment

[rosto]

It's ok and correct.
If i have the second column, I know how to solve the problem, but I receive the column A from accounting department, from other application and it have hundreds of rows, so i hoped that is not necessary to lick every cell to create a second column.