INDEX/MATCH or Equivalent to Return Data with Blank Cells

Hello -

I have received help in another thread for using this same workbook. One previous issue was resolved, but I didn't realize that another was created.

Quick background - I am looking to have cells H14 & I14 return the date when the H:H and I:I column is greater than or equal to 3 (or greater than 2, doesn't matter to me). The H:H and I:I columns are reverse cumulative sums that need to be there. I cleaned them up so that if it's a repeat number, the cell remains blank. This caused an issue for the INDEX/MATCH or LOOKUP or conditional statement (I tried them all, along with forum members help). For example, in the attached workbook, H14 shows 12/21/16... it should return 12/15/16; I14 shows 12/1/16... which is correct. Same formula, different ranges, different result.

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Additionally, this is dummy data. I am using the same type of data in a workbook with thousands of rows. I first noticed this problem when I had a large range (10000+), and then went back to look at this dummy data, which still doesn't do exactly what I intended.

It's not the end of the world if I cant have blank cells and have an INDEX/MATCH, LOOKUP, conditional statement work. The requirement is to return the MAX date when the H:H or I:I column is >=3.

Additionally, does Excel have some limitation on number of rows/cols that can be used tor INDEX/MATCH?

That was quick. Thank you!

This formula below works great in the dummy data in the workbook I provided. I tried it in my actual document using the formula below tailored to the range {=INDEX($A$3:$A$10000, MATCH(3,AM$3:AM$10000,-1))} and get the #N/A error. Any thoughts why?

Originally Posted by FDibbins

This seems to work...
=INDEX($A$3:$A$13,MATCH(3,H$3:H$13,-1))
Note you have calcs set to manual

This also works great on the workbook I provided. Thank you.

This also, unfortunately, returns the #N/A when I use it in the context of my document.

{=INDEX($A$3:$A$10000,MATCH(2,1/((AM3:AM10000>=3)*(AM3:AM10000<>"")),1))}

Any thoughts on how to make this work using the larger range?

If it helps, I really only need the rows to be a particular date range (today through 90 days in the past). The A column is the dates. Is there a way to make the rows populate based on the date to reduce the range? (i.e. if row 3456 is 90 days ago, is there some way to write the formula to say A3456:A____ whatever today's date is) Maybe some way to incorporate ROW() to reduce the range if it's necessary?

Originally Posted by FlameRetired

As an alternative try array entering

Formula:

Please Login or Register  to view this content.

Or try ...

=LOOKUP(10^307,$A$3:$A$13/(H3:H13>=3)/(H3:H13<>""))

Originally Posted by FDibbins

If it works on your dummy data but not your real data, then can you provide some more realist data to test against?
(btw, my formula was a regular formula, not an array formula, no need to enter using CSE)

As I was providing a larger data set, I realized my mistake (I asked the formula to look at the wrong range). Both the solutions from yourself and FlameRetired, as well as Phuocam all work.

Originally Posted by FlameRetired

The formula should work on the larger range.

Have you array entered it (Ctrl + Shift + Enter) and is there any value in AM3:AM10000 >= 3? If not that would return #N/A.

Yes, I did enter it as an array. I made a mistake and asked the formula to look at the wrong range. That's my fault . Problem solved, thank you all for the help!

Originally Posted by Phuocam

Or try ...

=LOOKUP(10^307,$A$3:$A$13/(H3:H13>=3)/(H3:H13<>""))

This also works great. Thank you!