Excel match function producing inconsistent results

Question

The following questions in stackoverflow.com were read but did not help:
1. excel formula - Inconsistent behavior for INDEX(MATCH())
2. excel - Why is Application.Match() is inconsistent?
3. Unpredictable output from Excel Index-Match
4. Excel match(less than) function behaves inconsistently
5. excel - Why is Application.Match() is inconsistent?
6. worksheet function - Inconsistent formula returns in Excel

What I need is to understand and correct the cause for the inconsistent behavior of the MATCH function when using match_type 1 in different LOOKUP_ARRAYs in a spreadsheet

To clarify even further the issue I pasted below the pertinent description of match function with match_type 1:

Match-type: 1 or omitted

Behavior: MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order

When I perform the following:

Command                                Result
---------------------------------------------

=CORRESP(96;{0;91,2;92,8;94,4;96};1)   5                
=CORRESP(72;{0;69;70;71;72};1)         5

The above results are correct, and I do not question them.

In the simple spreadsheet in the link I reproduce the usage of the match function =MATCH(LOOKUP_VALUE,LOOKUP_ARRAY,1) in four ranges: PVc, PV, TPDc and TPD, and with different values in several cells but the problem occurs only with LOOKUP_VALUE 72 in LOOKUP_ARRAYS TPDc and TPD.

I believe that the problem is clearly explained in the Excel file in the link.

https://1drv.ms/x/s!AlpItI7re-Sdi_NP8XD-TVxkPXVbhw

Your file/data example should be a reference but the problem/what you have tried/expected output should still be explained in the verbiage of the question. — Scott Craner, Oct 16 '18 at 14:31
Both cells X4 and X9 are blank so what do you expect returned and if you change the last argument to 0 you define a precise match... — Solar Mike, Oct 16 '18 at 14:31
When using the `1` as the third criterion in the MATCH the data in the range must be sorted ascending, You probably what `0` as the third criterion. — Scott Craner, Oct 16 '18 at 14:33
As Scott said - I can't access the link, so no idea what the question is about and will more than likely be a broken-link question in a few months/couple of years. — Darren Bartrup-Cook, Oct 16 '18 at 14:35
@solar-mike sorry. The "X" was there as a placeholder for letters "C" to "N". Yes! I should know better! — Paulo Andrade pandre45, Oct 16 '18 at 14:45
@Scott Craner can access the file correctly. Please try again. — Paulo Andrade pandre45, Oct 16 '18 at 14:49
@Scott the link worked alright for me. Can you please try again? — Paulo Andrade pandre45, Oct 16 '18 at 14:55
I could access. It was @DarrenBartrup-Cook who could not. But that does not change his other comment. Link only questions are not appropriate for this forum. Please explain the problem with examples of the formula and what it is doing wrong in the verbiage of the question with the link as backup not the whole question. — Scott Craner, Oct 16 '18 at 14:55
Firewalls stopped my access, not any particular problem with the link itself. — Darren Bartrup-Cook, Oct 16 '18 at 14:57
I suggest you read up about [Excel's `MATCH` documentation](https://support.office.com/en-us/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a). To broadly answer your question, when `MATCH` has a third argument of 1, `MATCH` performs a [binary search](https://en.wikipedia.org/wiki/Binary_search_algorithm) and finds the largest value that is less than or equal to the lookup value. Note that binary searches require your data to be sorted, or else you may get unexpected results. — ImaginaryHuman072889, Oct 16 '18 at 18:15
I did read up about Excel's MATCH documentation, @ImaginaryHuman072889. I suggest that you take a look inside the file and to the issue description. The issue is that it match is producing different results in equal situation. — Paulo Andrade pandre45, Oct 16 '18 at 19:30
As @ScottCraner pointed out, link questions are not appropriate for this site. Please include all relevant information in the question itself. Then the question can be taken off hold. — ImaginaryHuman072889, Oct 16 '18 at 20:20
As you have found MATCH will return the where the number is equal to or greater than the cell **AND** the numbers is not greater than the next cell. So it will return the last one in a list of identical numbers. — Scott Craner, Oct 16 '18 at 20:28
As others have stated, the formula `=MATCH(H4,PVc,1)` is behaving correctly according to the documentation. However, `=MATCH(H9,TPDc,1)` is _not_. Note that the first `72.0` value in the `TPDc` list is at index `5`, not `4` as returned by the formula. Also, when I re-enter `72` in cell `H9` the formula returns `12` as expected. For some reason I can't explain, Excel is seeing `H9` .. `N9` as being < the `72` 's in range `TPDc` (until the values are re-entered) — chris neilsen, Oct 16 '18 at 21:49
Thanks, @chris, you got the point! I could not understand it either. It is very unfortunate that other commenters did not look into the spreadsheet to do their analysis. — Paulo Andrade pandre45, Oct 17 '18 at 14:24
@ScottCraner, I fully rephrased the question and hope that it may now be released from hold. — Paulo Andrade pandre45, Oct 21 '18 at 03:45
@Darren Bartrup-Cook, I fully rephrased the question, may I ask you to release the question from hold? — Paulo Andrade pandre45, Oct 23 '18 at 01:57
@BigBen, I fully rephrased the question, may I ask you to release the question from hold? — Paulo Andrade pandre45, Oct 25 '18 at 01:49
@Tim Williams, I did a lot of research on similar questions and rephrased the whole question. Will you please release it from hold and try to help, if possible? — Paulo Andrade pandre45, Oct 25 '18 at 01:52
@pnuts, I did a lot of research on similar questions and rephrased the whole question. Will you please release it from hold and try to help, if possible? — Paulo Andrade pandre45, Oct 25 '18 at 01:52
The whole question was rewritten and research on similar questions was conducted and no helpful hint was found. Can someone help by releasing the question from hold and/or answering it? — Paulo Andrade pandre45, Oct 25 '18 at 01:54
@Honza provided the explanation for what happens, however the second part of my rquest is not answered. Although the explanation indicates why the inconsistency happens, it really does not solve the problem with the Match function. If you type in any cell of the attached spreadsheet "= 72 = H9" the result is True. This means that while **Excel determines that the two numbers are equals** the **Match function indicates they are not**. This is in my opinion **a major flaw in the Match function implementation**. Please, I need suggestions on how to deal with it. — Paulo Andrade pandre45, Nov 01 '18 at 12:54
@ScottCraner, I believe that my question deserves some of your attention, Will you be kind and provide new thoughts on it? Currently the cause for the error was determined, but Match function inconsistency remains. And no solution is provided. — Paulo Andrade pandre45, Nov 01 '18 at 13:10
@solar-mike, I believe that my question deserves some of your attention, Will you be kind and provide new thoughts on it? Currently the cause for the error was determined, but Match function inconsistency remains. And no solution is provided. — Paulo Andrade pandre45, Nov 02 '18 at 22:32
@Darren Bartrup-Cook, I believe that my question deserves some of your attention, Will you be kind and provide new thoughts on it? Currently the cause for the error was determined, but Match function inconsistency remains. And no solution is provided — Paulo Andrade pandre45, Nov 04 '18 at 02:35
@BigBen, I believe that my question deserves some of your attention, Will you be kind and provide new thoughts on it? Currently the cause for the error was determined, but Match function inconsistency remains. And no solution is provided — Paulo Andrade pandre45, Nov 04 '18 at 21:16
Sorry, still can't access the spreadsheet - firewalls at this end. This formula `=CORRESP(96;{0;91,2;92,8;94,4;96};1)` (`=MATCH(96,{0,91,2,92,8,94,4,96},1)`) in your post returns 8. I wouldn't expect it to return the correct answer as the lookup_array isn't sorted which it needs to be unless you're looking for an exact match. — Darren Bartrup-Cook, Nov 05 '18 at 09:13
@Darren Bartrup-Cook, sorry, I should have told you I am using Excel in Portuguese and the characters are different from the American convention (USA "." and "," are "," and ";" in Portuguese convention. Below are the commands in USA convention. CommandResult ------------------------------------------------------- =MATCH(96,{0,91.2,92.8,94.4,96},1) 5 =MATCH(72,{0,69,70,71,72},1) 5 — Paulo Andrade pandre45, Nov 07 '18 at 12:45
I've opened the spreadsheet on my phone - can't see the formula though. What is the formula you're using? I tried `=MATCH(B8,$B$8:$J$8,1)` in cell `B10` and dragged across but this gave me `1,2,3,4,9,9,9,9,9` in `B10:J10`. Not what your spreadsheet is showing though. — Darren Bartrup-Cook, Nov 07 '18 at 13:41
@Darren. If there is a way for me to send you the Spreadsheet just tell me. If you would like to inform me an e-mail. please send an e-mail to: pandre at techisa dot net. — Paulo Andrade pandre45, Nov 15 '18 at 15:49
@ScottCraner have you had time to look into the problemwith the Excel Match functio I described? — Paulo Andrade pandre45, Nov 24 '18 at 01:20

Honza Zidek · Answer 1 · 2018-10-23T11:39:50.430

There is an issue with the values stored in your Excel sheet. If I download it and unpack (just rename *.xlsx to *.zip), in the xl/worksheets/sheet1.xml file there is the following:

<row r="9" spans="1:26">
  <c r="A9" s="3" t="s">
    <v>8</v>
  </c>
  <c r="B9" s="5">
    <v>0</v>
  </c>
  <c r="C9" s="5">
    <v>67.833333333333314</v>
  </c>
  <c r="D9" s="5">
    <v>68.666666666666643</v>
  </c>
  <c r="E9" s="5">
    <v>69.499999999999972</v>
  </c>
  <c r="F9" s="5">
    <v>70.3333333333333</v>
  </c>
  <c r="G9" s="5">
    <v>71.166666666666629</v>
  </c>
  <c r="H9" s="5">
    <v>71.999999999999957</v>
  </c>
  <c r="I9" s="5">
    <v>71.999999999999957</v>
  </c>
  <c r="J9" s="5">
    <v>71.999999999999957</v>
  </c>
  ...
</row>

I do not know how your excel has been created, but apparently the stored numbers are not exact.

Just try to go to the cell H9 and type 72Enter. The cells H10 and H11 are then re-calculated and contain the expected result.

As Antoine de Saint-Exupéry advises:

What is essential is invisible to the eye.

In other words, if you feel that Excel gives you unexpected results, always go under the hood :) You may look at another example of weird behaviour which could be solved in the same way: https://stackoverflow.com/a/49731873/2886891

You are right Honza, I changed the value in cell H9 as you indicated and got right result. But now Excel is producing wrong results. If before typing 72 in cell H9 you just type in any other cell "=72 = H9" returns TRUE. — Paulo Andrade pandre45, Oct 30 '18 at 22:54
Although your explanation indicates why this inconsistency happens, it really does not solve the problem with the Match function. If you type in any cell _"= 72 = H9"_ the result is **True** indicating that there is a real defficiency in the Excel Match function. Is there a way to avoid this problem? — Paulo Andrade pandre45, Oct 31 '18 at 18:13
I will have a look as soon as I get back to my Windows computer with Excel in a few days. — Honza Zidek, Nov 01 '18 at 13:22

Excel match function producing inconsistent results

1 Answers1