Nearest Match on descending scale

[RojParody]

Good morning,

Been lurking for a while and managed to uncover some great answers to things I needed, but can't seem to find anything for this question.

I have a table with four columns (Code; City; Site; Group). The "Code" column contains a list of numbers, ranging from 3-digit numbers to 11-digit numbers.

I am trying to search for the match for an 11-digit number, matching 11-digits if possible, if not then matching the first 10, then first 9, on a sliding scale.

For example, if I have the following:

Code City Site Group

0123 City 1 Site A Group A
012345 City 2 Site B Group A
01234567 City 3 Site C Group B

I want to search for a match to 01234567890, and return the Site, for example:

01234567 City 3 Site C Group B = "SITE C"

I don't know if this is possible, but thought I'd ask before giving up altogether!

Roj

 

[RojParody]

Hi Mike,

If I try this one:

=INDEX($C$2:$C$20, MATCH(MIN(IF(LEFT($A$2:$A$20&REPT("0",11), 11) < LEFT(F3&REPT("0",11), 11), LEFT($A$2:$A$20&REPT("0",11), 11)-LEFT(F3&REPT("0",11), 11))), IF(LEFT($A$2:$A$20&REPT("0",11), 11) < LEFT(F3&REPT("0",11), 11), LEFT($A$2:$A$20&REPT("0",11), 11)-LEFT(F3&REPT("0",11), 11)), 0), 1)

... I get the same outcome for all cells.

 

[RojParody]

Hi Jason,

That seems to work, even when entering new control numbers into column F, as long as the A column is 5-digits or longer.

When entering a number into column F designed to pick up a 4-digit number in column A, the output doesn't change.

The formula you have given is the same alongside 4-digit rows as it is to the others, but just doesn't seem to work?

Is there something I'm missing?

 

[jasonb75]

Assuming the same data as the screenshot in post #8, please post the control number entered in column F that is giving the wrong result.

 

[RojParody]

Hi Jason,

It's the number in F3 (11561256987). It should bring up an outcome of 68, but shows 95 each time I try enter it (even when building sheet from scratch).

 

[RojParody]

Hi All,

A solution has been found (a friend of mine has been obsessing about this all morning!)

Based on the same screen shot from post 8, in column H is the following formula:

=MAX(COUNTIF($A:$A,LEFT($F2,11))*11,COUNTIF($A:$A,LEFT($F2,10))*10,COUNTIF($A:$A,LEFT($F2,9))*9,COUNTIF($A:$A,LEFT($F2,8))*8,COUNTIF($A:$A,LEFT($F2,7))*7,COUNTIF($A:$A,LEFT($F2,6))*6,COUNTIF($A:$A,LEFT($F2,5))*5,COUNTIF($A:$A,LEFT($F2,4))*4,COUNTIF($A:$A,LEFT($F2,3))*3)

Then, in column G is: =VLOOKUP(VALUE(LEFT(F2,H2)),$A$2:$D$20,3,FALSE)

With various control numbers tested, this is now working.

Thanks for all of your help though guys! Much appreciated.

Virtual smiles for all of you

 

[jasonb75]

Hi Jason,

It's the number in F3 (11561256987). It should bring up an outcome of 68, but shows 95 each time I try enter it (even when building sheet from scratch).

Looks like you didn't sort the data by column E after adding the helper formula, if you do that, it should work fine