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
– Scott Craner, Commented Oct 16, 2018 at 14:31
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
– Scott Craner, Commented Oct 16, 2018 at 14:55
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. — pandre45
– pandre45, Commented Oct 16, 2018 at 19:30
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
– chris neilsen, Commented Oct 16, 2018 at 21:49
@ScottCraner, I fully rephrased the question and hope that it may now be released from hold. — pandre45
– pandre45, Commented Oct 21, 2018 at 3:45

Honza Zidek · Accepted Answer · 2018-10-23 11:39:50Z

1

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

edited Oct 23, 2018 at 11:39

answered Oct 23, 2018 at 11:29

Honza Zidek

21.8k10 gold badges97 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pandre45 Over a year ago

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.

pandre45 Over a year ago

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?

Honza Zidek Over a year ago

I will have a look as soon as I get back to my Windows computer with Excel in a few days.

Collectives™ on Stack Overflow

Excel match function producing inconsistent results

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related