| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula or Macro
Yep
"A.S." wrote: 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" wrote: 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." wrote: 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" wrote: 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,L EN(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,MAT CH(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),L EN(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,MA TCH(LARGE(IF(--(LEFT(E1,LEN(A$1:A$5)))=A$1:A$5,LEN(A$1:A$5)),1),L EN(A$1:A$5)*(--(LEFT(E1,LEN(A$1:A$5)))=A$1:A$5),0)),"") "A.S." wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Formula for Max value | Excel Worksheet Functions | |||
| Macro Formula for Max value | Excel Worksheet Functions | |||
| Help with a macro/formula | Excel Worksheet Functions | |||
| Macro/Formula Help? | Excel Discussion (Misc queries) | |||
| Do I need a formula or Macro? | Excel Worksheet Functions | |||
Threaded Mode
