Formula or Macro

[A.S.]

Hello,
I am looking for a formula or macro that will help me do the following:

Let's say I have data as:

542 A 2.5
544 B 2.8
5423 C 1.9
5421 D 1.4
54233 E 1.9

In another data set I have I am running an INDEX/MATCH so I can get the 3rd
row value.

So somewhere else I may have

542 J 2.5
544 Z 2.8
5423 Y 1.9
54214 R ?
542332 S ?

Given this, the last 2 would not find a match, however, what I would like
them to do is to roll up and find the nearest match so that, 54214 would
recognize that it matches the 5421 in the other data set before it reaches
the 4 and would enter 1.4 and the second one would find the match at 54233
before the 2 and would enter 1.9

 

[JMB]

assuming your first table is in A1:C5, and the number to look up is in E1,
you could try:

=INDEX(C$1:C$5,MATCH(LARGE(IF(LEFT(E1,LEN(A$1:A$5))=A$1:A$5,LEN(A$1:A$5)),1),LEN(A$1:A$5)*(LEFT(E1,LEN(A$1:A$5))=A$1:A$5),0))

or with error trapping:
=IF(SUM(--(LEFT(E1,LEN(A$1:A$5))=A$1:A$5)),INDEX(C$1:C$5,MATCH(LARGE(IF(LEFT(E1,LEN(A$1:A$5))=A$1:A$5,LEN(A$1:A$5)),1),LEN(A$1:A$5)*(LEFT(E1,LEN(A$1:A$5))=A$1:A$5),0)),"")

array entered w/Cntrl+Shift+Enter. This is assuming the numbers you are
trying to match are formatted as text. If they are formatted as general or
numeric, try:

=INDEX(C$1:C$5,MATCH(LARGE(IF(--(LEFT(E1,LEN(A$1:A$5)))=A$1:A$5,LEN(A$1:A$5)),1),LEN(A$1:A$5)*(--(LEFT(E1,LEN(A$1:A$5)))=A$1:A$5),0))

with error trapping:
=IF(SUM(--(--(LEFT(E1,LEN(A$1:A$5)))=A$1:A$5)),INDEX(C$1:C$5,MATCH(LARGE(IF(--(LEFT(E1,LEN(A$1:A$5)))=A$1:A$5,LEN(A$1:A$5)),1),LEN(A$1:A$5)*(--(LEFT(E1,LEN(A$1:A$5)))=A$1:A$5),0)),"")

 

[A.S.]

Thanks JMB! Your error trapping version of non-text data looks like it worked!

 

[A.S.]

JMB, I want to make sure that the INDEX/MATCH is performed first so that if
there is an ISNA, then that is when it looks for the next match. Is this how
the formula works?

 

[JMB]

I'll try to explain the approach I'm using:

If you're trying to match 54214 to the table in your example, the formula
pulls the left n characters from the number you are trying to match where n =
the length of the numbers in your table. So the comparison would look like:

542 = 542
544 = 542
5423 = 5421
5421 = 5421
54233 = 54214

If the numbers match, the formula returns the length of the data in A1:A5,
otherwise it returns FALSE. So the comparison results in an array that
looks like

3
FALSE
FALSE
4
FALSE

The largest number is obtained using the LARGE function and Matched to the
above table to get the relative position of the match containing the most
charactes in the list. Coincidentally, the length of the best match and its
position in the table is 4, which is passed to Index to return the required
info

Exact matches should be picked out before approximate matches because the
LARGE function selects the one with the most number of matching characters
and smaller strings/numbers won't match to any of the larger strings/numbers
in A1:A5. For example, 542 would match to both 542 and 54 if they were in
your list in A1:A5, but would not match to 5423. Since 542 has more matching
characters (3) than 54 (2), 542 is selected as the best match (which is also
an exact match).

Leading/Trailing spaces in the data would cause problems, though.

 

[A.S.]

So correct me if I am wrong, but from what is sounds like then exact match is
used first and then it goes to find a match for those that are not exact
matches?

 

[JMB]

Yep

So correct me if I am wrong, but from what is sounds like then exact match is
used first and then it goes to find a match for those that are not exact
matches?