Dynamic INDEX MATCH row & column

[CunningFoxDemon]

Hello,

I'm trying to identify the last entry in a row using INDEX,MATCH. I'm able to pull the dynamic row, but as I'm unable to have the formula identify the last column in that same row, I am forced to overwrite the data instead of adding a new date's data in a new column.

Can someone help me to correct the below formula to find a dynamic column that finds the last non-blank entry of a dynamic row?

=INDEX('Materials (V)'!$A$2:$FG,MATCH(A3,'Materials (V)'!$A$2:$A,0),2)

Materials (V)!A2 holds the item with the first value entry in B2. In other words "2" in the column variable is finding the first date's data. The 2nd date's data would be in column 3. How do I dynamically call column 3 of that specific row?

Also, if column 3 doesn't have data in it for the specific row, but the next day it does, say column 4, how do I get it to find that value?

Thank you for your time and support. Any help is much appreciated.

 

[Jonmo1]

Lookup assumes the data is sorted in ascending order.
It doesn't do anything to 'verify' if the data is actually sorted or not. It just flat assumes and proceeds as if it is.
So lookup will find the largest value that is less than or equal to the lookup value.
Normally, because it's assumed to be sorted ascending, lookup continues looking to the right until it finds a value larger than the lookup value.
And then returns the previous one as the largest value that is less then or equal to the lookup value.

By using the bignum, we are guaranteed that the lookup value will be greater than or equal to any possible value in the range.
So it keeps looking to the right for a larger value.
When it can't find any a value 'greater than' the bignum, it then assumes that the last value IS the largest value less than or equal to the lookup value.

 

[CunningFoxDemon]

I see. Thanks so much for the help. Take care!