VLookup or Match?

[dgm511]

Hi,


Struggling to find a formula that allows me to search through a column of 12 digit numbers against another column of 12 digit numbers where the last 3 digits of each number differs. e.g


search

Column A

100434890152

100439740108

100439860105


match the first 9 digits of (in this case the third number in the list) to a second column of 12 digit numbers i.e.


Column B

100439840252

100439860202

100439870250


and return, in this case the second number in the list.


The first 9 digits of each number are unique in both columns so basically I'm trying to work out a formula that will read the first 9 digits of Column A, match it with a number from Column B that has the same first 9 digits and result the full 12 digit number from column B.


I was hoping that the VLOOKUP using the TRUE parameter would work but it just appears to result in the bottom number from column B despite sorting both columns into ascending order and checking both columns are numbers rather than text or general.


I use a match and index formula - IF($A3<>"",INDEX(Sheet1!$B:$B,MATCH($A3,Sheet1!$A:$A,0),1,1),"") to return data when the numbers or data in the 2 columns match however in this case it is just the first 9 digits that are unique in both columns.


Any help would be extremely gratefully received as has being taxing my brain for a few hours now!


Many thanks

 

[NARAYANK991]

Hi ,


You can use this construct :


=INDEX(Column_B_numbers,MATCH(LEFT(Column_A_number,9),LEFT(Column_B_numbers,9),0))


entered as an array formula , using CTRL SHIFT ENTER.


Narayan

 

[dgm511]

Perfect! Very many thanks for your help.

 

[dgm511]

I had never hear of an array formula so will enjoy looking up posts on it to try and understand them further.

Many thanks again

 

[Trader273]

Arrays can seem a little intimidating at first since it's something a little different that requires a little different thinking. But once you really get into them, you will probably use them a lot more than you would have realized.