multi variable lookup across numerous ranges

boriss321 ,

We assume all your data in first hundred rows and columns from A to AZ. With

=INDEX(A1:AZ100, RowNumber, ColumnNumber)

we find resulting value in that range based on our 3 criteria.

Row number is calculated as

=MATCH($H$14,OFFSET(A1,,MATCH($H$16,1:1,0)-1,100),0

Here

=MATCH($H$16,1:1,0)

finds position of the cell with customer name (H16) in the row 1. Let assume Customer 3, when it returns 9, i.e. that's 9th column (I).

After that we return first 100 values from that column with

OFFSET(A1,,MATCH($H$16,1:1,0)-1,100) or
OFFSET(A1,,9-1,100)

In returned range we find position of the test (e.g. Test 2) with

MATCH($H$14,OFFSET(A1,,MATCH($H$16,1:1,0)-1,100),0) or
MATCH($H$14,I1:I100,0)

which returns 7. That is our row number.

Column number.

With nested

=MATCH(1,INDEX(--(INDEX(1:1,MATCH($H$16,1:1,0)):AZ1=""),0),0)

we find position of the first empty cell in row 1 starting from the column with our customer name

Here with

INDEX(1:1,MATCH($H$16,1:1,0)):AZ1

MATCH returns the position for it in the first row, INDEX returns the value in it, but in syntax as INDEX(...):AZ1 it will be equivalent of I1:AZ1. After that in INDEX(--(I1:AZ1=""),0) we first check if cell is empty or not (TRUE or FALSE), double dash converts it 1 or 0 and finally this INDEX returns array as {0,0,0,0,1,1,...}.

Next MATCH works as MATCH(1,{0,0,0,0,1,1,...},0) and returns position of the first empty cell. In our case 5.

Adding +MATCH($H$16,1:1,0)-2) we receive absolute column position of the of the last not-empty column for that customer block, other words position of the cell with the last defined date (12 for L1).

Finally with concept of dynamic range the

INDEX(1:1,MATCH($H$16,1:1,0)):INDEX(1:1,,MATCH(1,INDEX(--(INDEX(1:1,MATCH($H$16,1:1,0)):AZ1=""),0),0)+MATCH($H$16,1:1,0)-2)

will be equivalent of I1:L1

Thus the

MATCH($H$15,INDEX(
      INDEX(1:1,MATCH($H$16,1:1,0)):INDEX(1:1,,MATCH(1,INDEX(--(INDEX(1:1,MATCH($H$16,1:1,0)):AZ1=""),0),0)+MATCH($H$16,1:1,0)-2),
   ),1)

is converted to

MATCH($H$15,I1:L1,1)

which with last parameter 1 returns in our dynamic range the position of the first date which is less than defined in our criteria (3). With adding first position of the block

=MATCH($H$15,I1:L1)+MATCH($H$16,1:1,0)-1

we receive absolute position of our column (11).

Thus our entire formula is evaluated as

=INDEX(A1:AZ100, 7,11)

which returns desired result.

boriss321 , sorry to hear that, hope all your issues are in the past.

Sure, I'll update with details, most probably tomorrow.

Good luck.