1

I'm having list of 1000 numbers (10 digits) in each rows, need to get(retrieve) that particular number from that data by entering last four digits alone..

Just I have to retrieve the 10 digits by simply enter last 4 digits in another rows

Ps :

Column A
123456
456756
556678

Column B :
If I enter last three digits from row A, column should be automatically auto fill

  • 456 should come as 123456
  • 756 should come as 456756
  • 678 should come as 556678
Máté Juhász
  • 22,071
Yuvaraj Aravind
  • 11

1 Answers1

0

There are a couple of tricks to retrieve a full number based on a partial set of digits. Say we have 10 digit values in column A from A1 through A22. Say we want to retrieve one of these based on the last three digits (which we place in cell B1)

In C1 enter the array formula:

=INDEX(A1:A22,MATCH(TEXT(B1,"General"),RIGHT(A1:A22,3),0))

enter image description here

The tricks are:

  1. because we are using RIGHT inside MATCH, we need an array formula.
  2. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.
  3. because RIGHT returns Text, we must convert B1 into Text using the TEXT function.
Gary's Student
  • 19,636