HLOOKUP & VLOOKUP Combinded
I have tried Index & Match combined, but the data changes rows week to
week, and when I use the match formula it will only look for data along
the same line.
If I understand correctly, there are pairs of kid/date columns side by side.
As an example using Excel 2003, there are eight pairs in A1:P19, including the header row A1:P1.
In R1 is the kid to be matched.
In R2 is the date to be matched.
Then this formula seems to work:
=OFFSET($A$1,
MATCH($R$1,OFFSET($A$1,1,MATCH($R$2,A1:P1,0)-2,18,1),0),
MATCH($R$2,A1:P1,0)-1)
If there can be more rows, add to the 18. If there can be more columns, change the P1's and move R1 & R2 someplace else.
To account for "no match" situations, you might want to try something like:
=IF(ISERROR(FormulaAbove), "", FormulaAbove)
Hope this helps getting started.
|