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
  • 21,403
  • 6
  • 54
  • 73
user1092590
  • 11
  • 2
  • 1
    Use the search function – Andreas Oct 28 '19 at 08:18
  • 1
    In my opinion, VLOOKUP with "*" symbol can help you, but I also suggest you provide the sample about this problem. – Lee Oct 28 '19 at 09:46
  • You need VBA, if you want to enter partial data in a cell, then replace that same cell with the full entry. Functions may work if want to enter the partial data in one cell, then have another cell give you the full entry. good luck. – gns100 Oct 28 '19 at 18:10

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,266
  • 6
  • 25
  • 39