Forum Discussion
multi variable lookup across numerous ranges
txrussianguy , that could be better to transform your customers data into the tables or named ranges and work with them. Without that the formula could be like
=INDEX(A1:AZ100,
MATCH($H$14,OFFSET(A1,,MATCH($H$16,1:1,0)-1,100),0),
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)+
MATCH($H$16,1:1,0)-1)
assuming all your data is within A1:AZ100 range.
-First MATCH finds the number of tests for the customer (we may take entire column but I guess you have another data under the tests)
Second MATCH finds the month which is not greater than given one. Within it we take the range (manipulating with INDEX) from customer name to first non-blank cell in row for that customer (if take entire row we could find month for another customer).
SergeiBaklan, Only tested with a few examples, but holy moly. You seem to have cracked it. Let me work on understanding the sections to decipher. If (more likely "once") I run into a roadblock in understanding, will reach out and tell you where I'm stuck.
But already am on knees bowing forehead to floor. This is fantastic!
SergeiBaklan, my apologies for a long absence. I got hit with a string of personal issues (deaths, broken limbs) in my immediate circle, and fell off completely from this project. Back now, trying to get a handle on things again. Could I ask of you to walk me through the basic flow of the formula, and what it evaluates? I just want to make sure I'm clear on what the core blocks of it are doing. Thanks so much for any clarification on order and logic.
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.
SergeiBaklan, incredibly helpful. Thank you so much.
boriss321 , sorry to hear that, hope all your issues are in the past.
Sure, I'll update with details, most probably tomorrow.
Good luck.
txrussianguy , for better understanding you may check components of above as separate formulas with using Evaluate formula
