Match Function Not Returning Right Value

[Gallegos]

I am using the Index/Match function to find a value in an array and I am not sure why the match
function is returning the wrong value. The data is as follows:

Search array:

A1 = 0
B1= 9999
C1= 0
D1= 0
E1= 0


Formula using: Match(221,A1:E1,1)

Based on what I read on how the match function is to work, the match type of 1 would find the
largest value that matches the lookup value exactly or less than. Thus, it seems to me that
the formula I have above should return the value of "1" because 221 is less than 9999. But, it
is returning the value "5". Why is that?

[sweep]

Hi,

For the ",1" argument to work, the lookup range needs to be in ascending order.

https://support.microsoft.com/en-us/...rs=en-us&ad=us

[MrShorty]

The help file (https://support.microsoft.com/en-us/...9-533f4a37673a ) also explains that, when the 3rd argument is 1, the lookup values must be sorted in ascending order. In your example, they are not sorted, so the MATCH() will return incorrect results. I expect that internally MATCH() makes the observation that 221 is larger than the last (5th) entry in list (which is 0) and is returning 5 to reflect that observation.

Solution is to sort row 1 left to right so that the 4 0s are in A1:D1 and E1 is 9999. If that is not possible, then you need a different approach to the lookup.

[Gallegos]

Thank you very much for the link and the guidance. Sometimes Excel doesn't work as one would think it would.

[Gallegos]

Thank you for your reply and solution. I entered the data as you had indicated & Excel comes back with position 4,
which I guess makes sense insofar as 4 is the "next lowest value" as referenced in the link to Microsoft support.
So, I just have to restructure how I go about getting the value.