Array formula not working for full worksheet

[NBrown1]

Hello,

I am using an index match match formula to return the latest percentage that corresponds with month and item #.

Currently the formula I am using is:

=IFERROR(INDEX('21 Twin Walls'!$B$3:$EC$63,MATCH(Sheet2!K$2,'21 Twin Walls'!$B$3:$B$63,1),MATCH(Sheet2!$A7,'21 Twin Walls'!$B$3:$EC$3,0)),)

The formula should be returning 20% in K10, but nothing is showing. However, when I change

MATCH(Sheet2!K$2,'21 Twin Walls'!$B$3:$B$63,1)

to

MATCH(Sheet2!K$2,'21 Twin Walls'!$B$3:$B$50,1)

the formula returns the correct 20%.

It seems to me I might be doing too big of an array, but I'm not sure why this would be.

Thank you for your assistance

Sample Workbook Full.xlsx

[hoyasaxa215]

Hmmm, there's a problem b.c of the external links. Regardless, what worksheet/cell contains the equations above? And which worksheet contains the 20% in K10 that you said should be the correct result?

[NBrown1]

I figured that might be an issue.

The equation belongs in cells K:AQ for every column that is labeled with the green progress bar. For this example that equation is in cell K10.

The 20% comes from cell L7 on the 21 Twin Walls tab. The equation should look for the item # on the left and the month on the top, then return the latest value from 21 Twin Walls.

Sorry if this is confusing, please let me know if you have and other questions.

[Jonmo1]

When using the 1 option as the 3rd argument of Match, it's doing a "Closest Match" lookup.
This requires the data in the array to be sorted in Ascending Order.
Your data in column B is NOT sorted in ascending order.
There are blank spaces between each subset of dates.

[Jonmo1]

Even if we don't consider the blanks...
The values themselves are also not sorted in ascending order.
They are NOT dates, they're just text strings.

So around row 28 you go from
12-13 to 01-14

Since they are text strings, the 0 in 01-14 is considered lower/before the 1 in 12-13, so it's not in ascending order.

[NBrown1]

Jonmo,

Thank you for your detailed response. I have eliminated the blank spaces, and changed all the numbers to 'short dates' to recognize the ascending order.

Even after doing this, I am having the same issues. I guess I'm just confused why the formula is only working part of the time.

[Jonmo1]

Can you re-post the updated book?

[Jonmo1]

Here's the 'why' it doesn't work...

Using the "closest match" type in the MATCH function
It Assumes the data is sorted in ascending order.
It's important to understand that it just 'Assumes' this, it doesn't do any test to verify if it actually is or not, it just proceeds on the assumtion that it is sorted ascending.

The closest match (also called a binary search) looks for the largest value that is 'less than or equal to' the lookup value.
So on the assumption the data is sorted in ascending order, it doesn't have to actually look at every value in the range.
It basically cuts the range in half, and tests if the last value in the first half is less than or equal to the lookup value.
if that value is Larger than the lookup value, then it knows the largest value less than or equal to the lookup value is in the first half.
if that value is Less than or equal to the lookup value, then it knows the largest value less than or equal to the lookup value could still be in the 2nd half.

So it picks whichever half it decides contains the value based on that test, and repeats that process again on that half.
Each time cutting the range it looks in by half, and testing the last value of the first half.

Until it finds the largest value less than or equal to the lookup value.
Basically a process of elimination.


Now when the data is NOT sorted in ascending order, that completely throws off that process of elimination.
And you get all ranges of strange and wierd results.
Sometimes it gets the desired result, but it's entirely a lucky coincidence.

[NBrown1]

Jonmo,

After reviewing your post, I realized the reason it was working for me some of the time was in fact a lucky coincidence.
It seems by adding additional months, and making them all in the same format, I have isolated the issue.

When I put percentages in the month, and it is NOT the lowest iteration of that month (i.e. 15% in cell L15 of 21 Twin Walls) this percentage wont return in cell U10 of Sheet 2. However, when I do put it in the lowest iteration of the month (L16) it does return correctly in U10.

Do you happen to know how I can work around this? I have attached the updated workbook.

Thanks for all the help.

Sample Workbook Full.xlsx

[Jonmo1]

The 'why' it does that is an extention of how the binary search works.
With the data sorted ascending, using the "Closest Match" type
If there are multiple occurances of the lookup value in a row, it will always return the furthest one down (or right if it's a horizontal range)

So there are 3 occurances of Oct-13.
The furthest one down is in B16
So it returns the corresponding value from column L.. L16, which is blank.
It's actually returning as 0, but you have 0's hidden in Excel's advanced options.

to get around this, we need more information.
In your mind, if there are multiple occurances of your lookup value (Oct-13), And multiple corresponding values in column L,
How do you choose which value from L you want to return?

[NBrown1]

For all months I will always want to return the latest value. For August you will see values in L11 and L12, I will always want to return the latest value, so L12.

[Jonmo1]

That's going to require some thought, and I'm out of time today.
Sorry. I'll try to pick it up tomorrow.

[NBrown1]

Jonmo,

No worries at all! Thanks for all your help thus far.

[Jonmo1]

Try this in K10
=IFERROR(LOOKUP(2,1/(('21 Twin Walls'!$B$5:$B$48=K$2)*(INDEX('21 Twin Walls'!$K$5:$EC$48,0,$BC10)<>"")),INDEX('21 Twin Walls'!$K$5:$EC$48,0,$BC10)),)

To reduce calcluations, I added a helper column in column BC
BC10: =MATCH($A7,'21 Twin Walls'!$K$3:$EC$3,0)

[NBrown1]

That works perfectly! Thank you so much!

[Jonmo1]

You're welcome.