Last 4 digits in different columns

[Muthukrishnan V]

Excel 365

Last 4 digits of Account numbers in different columns

My worksheet contains Account numbers in numericals in
columns B, G and T.

My requirement: For example, last 4 digits of 2372 is in B36, G52 and
T156. I want a formula to show me the cell references
like B36, G52 and T156 if I search for last 4 digits
with 2372. Is it possible to do this with helper for searching?

How to do this with formula? Thanking you.

 

[Joe4]

I don't have the exact answer you are looking for, but another alternative (and since no one else has responded yet, I will post it here).

You could use Conditional Formatting to highlight the values meeting your criteria.
For example, let's say that the value you want to search for is in cell A1. To highlight the instances in column B where the last four digits of the entry match the value in cell A1, select column B and enter this Conditional Formatting formula:

=RIGHT(B1,4)=TEXT($A$1,"0000")

and then choose your desired formatting option (i.e. color fill).

Then, you would just repeat this process for the other two columns, and it should highlight all values in those three columns meeting that critiera.

 

[Joe4]

Note if you truly want a solution that returns cell addresses, the only way I can think of how to do that is via VBA (there may be a formulaic solution too, but it is beyond my level of formula-expertise!).

If you would like to see this VBA solution, please provide us with the following details:
1. Where this 4 digit value that you want to check for exists (in some certain cell, being asked for via an Input, etc)?
2. Where you want the results (cell addresses) to appear (in some cell, in a MsgBox, etc)?

 

[Muthukrishnan V]

Thank you Sir