multi variable lookup across numerous ranges

[txrussianguy]

In the attached, I'm looking for the red/white cell to do the following:

1) based on the selection in H16 (Customer Selection), look across the top until it find that range of assumptions

2) based on the selection in H14 (Row Item), look down the left-most column of the relevant range

3) based on the selection in H15 (Which Answer), look across to the proper column

Caveats:

- dates across the top of each region are in order, but not consistent (not the same for each section)

- if exact date is not found, need the lesser date's value to be taken

- if needed (but preferred not) there can be a consistent number of month columns, if that helps define the width of each range within the formula

- Customers across the top won't be in any particular order, but if helpful, formula can rely on the fact that the list of customers (in defined "CustomerList" range) will include all of the options that could appear across the top to identify the ranges

- list of row items may fluctuate between customer groups (meaning similar to dates, each range will have a mostly consistent set of row items, but not necessarily identical

- there will be a constantly updated set of customers (meaning CustomerList and copies of the ranges...copied to the right a number of columns away from the last one), to accommodate a growing set of assumptions

Hope someone can tweak this to work. Greatly appreciate it.

[#2]

@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).

[#3]

@Sergei Baklan, 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!

[#4]

@txrussianguy , for better understanding you may check components of above as separate formulas with using Evaluate formula

[#5]

@Sergei Baklan, 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.

[#6]

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

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

Good luck.

[#7]

@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.

[#8]

@Sergei Baklan, incredibly helpful.  Thank you so much.

[#9]

@boriss321 , you are welcome