Forum Discussion

txrussianguy's avatar
txrussianguy
Brass Contributor
Apr 07, 2019

multi variable lookup across numerous ranges

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.

excel
Formulas and Functions

8 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 08, 2019

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

     

    • txrussianguy's avatar
      txrussianguy
      Brass Contributor
      Apr 08, 2019

      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!

      • boriss321's avatar
        boriss321
        Copper Contributor
        May 16, 2019

        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.

Resources