Hi,
Posting my workbook on Google Docs as it will be impossible to explain otherwise...
https://docs.google.com/spreadsheets/d/1NIm7gY2FvcgehehyFTUuJETvU2DgQv-O/edit?usp=sharing&ouid=100510883221888486550&rtpof=true&sd=true
Tab IWRS is the source, tab Dashboard is the output; the actual formulas are on tab Calc.
In column Z I have a two-dimensional lookup, formula
It works well as long as there are no duplicates in visit date. However if there's 2 visits for the same subject, on the same day (see rows 33/34), the formula is stuck. How can this be addressed? It may as well be a different type of formula, it's the best I could produce so far...
Formula in column G is also stuck, I'm not too worried about that one, but if this can be fixed as well it would be awesome.
Posting my workbook on Google Docs as it will be impossible to explain otherwise...
https://docs.google.com/spreadsheets/d/1NIm7gY2FvcgehehyFTUuJETvU2DgQv-O/edit?usp=sharing&ouid=100510883221888486550&rtpof=true&sd=true
Tab IWRS is the source, tab Dashboard is the output; the actual formulas are on tab Calc.
In column Z I have a two-dimensional lookup, formula
Excel Formula:
=INDEX($I$2:$W$751,MATCH(A2,$A$2:$A$751,0),MATCH((XLOOKUP(E2,I2:W2,$I$1:$W$1)+1),$I$1:$W$1,0))
Formula in column G is also stuck, I'm not too worried about that one, but if this can be fixed as well it would be awesome.