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

 

[Andrew Poulsom]

Welcome to MrExcel.

Given your sample data and 01234567890 in E1, you can use:

=VLOOKUP(E1,A2:C4,3)

The formula assumes that all the numbers are real numbers formatted to display leading zeroes.

 

[RojParody]

Thank you Andrew, but this isn't coming up with the correct return. It's searching the table, and coming up with something based on a different row.

I have attached an example pic below of the table I have.

In this instance, I need the formula to search F2, and return "95" as an outcome (based on the number in F2 being the closest match to row 11).

 

[RojParody]

Thank you Andrew, but this isn't coming up with the correct return. It's searching the table, and coming up with something based on a different row.

I have attached an example pic below of the table I have.

In this instance, I need the formula to search F2, and return "95" as an outcome (based on the number in F2 being the closest match to row 11).

Apologies for the sloppiness... I had taken the screen shot before I sorted column A by number order - however it still didn't bring up what was wanted.

 

[Andrew Poulsom]

That doesn't seem to be sorted ascending by column A.

 

[mikerickson]

How about
=INDEX(C2:C12, MATCH(MIN(ABS(LEFT(A2:A12&REPT("0",11), 11)-LEFT(F2&REPT("0",11), 11))), ABS(LEFT(A2:A12&REPT("0",11), 11)-LEFT(F2&REPT("0",11),11)), 0), 1)

Entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[mikerickson]

Andrew, the list is alphabetized, not sorted as numbers.

 

[RojParody]

Hi Mike,

Thank you for that, it's getting closer!

Here's another screen shot with you, with your formula included.

In this example, the outcome in G2, G3 and G4 are correct.

However, the outcome of G5 should be 95 (If you go back, the first exact match to the start of the number is in A7); and G6 should 28 (again, first exact match to the start is in A6)

Is this possible?

 

[mikerickson]

Try replacing the two instances of
ABS(LEFT(A2:A12&REPT("0",11), 11)-LEFT(F2&REPT("0",11), 11))

with

IF(LEFT(A2:A12&REPT("0",11), 11) < LEFT(F2&REPT("0",11), 11), LEFT(A2:A12&REPT("0",11), 11)-LEFT(F2&REPT("0",11), 11))

 

[jasonb75]

Seems to simple after looking at Mike's suggestion, but maybe

In E2 =--(A2&REPT(0,11-LEN(A2)))

Fill down and sort data by column E, ascending values

In G2 =INDEX(C:C,MATCH(F2,E:E))

Will fail if you have the helper formula in column E with an empty cel in column A