Formula to look across rows and columns in a table.

al@ki

Hi there I need a formula that will look across rows and columns in a table. 

For example,  I need the result for School District-B with 23 items. The result from the table below should populate with: $350

                                         1-5       6-15    16-25    26-50   51-100    100-50000

School District-A               $100    $200    $300    $400    $500        $600

School District-B               $150    $250    $350    $450    $550        $650

School District-C               $200    $300    $400    $500    $600        $700

Charter School-A              $100    $200    $300    $400    $500        $600

Charter School-B              $150    $250    $350    $450    $550        $650

Charter School-C             $200    $300    $400    $500    $600        $700

Thanks!

 

Chak Khiam

There might be more elegant way to write the formula. How I do it would be:

Table layout as you have provided. Using column names: Zone, Pack1 to Pack 6.

Entry cells are Zone9 and [Pack1]9 for "School District-B" and "23" respectively:

 

First, find the "Pack" (column name for 1-5, 6-15, etc in Row1) which the quantity 23 belongs to, using this formula

=IF(AND($[Pack1]$9 > 0, $[Pack1]$9 < 6), $[Pack1]$1,

     IF(AND($[Pack1]$9 > 5, $[Pack1]$9 < 16), $[Pack2]$1,

       IF(AND($[Pack1]$9 > 15, $[Pack1]$9 < 26), $[Pack3]$1,

         IF(AND($[Pack1]$9 > 25, $[Pack1]$9 < 51), $[Pack4]$1,

           IF(AND($[Pack1]$9 > 50, $[Pack1]$9 < 101), $[Pack5]$1,

             IF(AND($[Pack1]$9 > 100, $[Pack1]$9 < 50000), $[Pack6]$1))))))

 

Next, insert the above (in italics below, minus the "=" sign) as Search Values into the MATCH function to get an appropriate Column Index.

=INDEX(Zone1:[Pack6]7,

      MATCH(Zone9, Zone1:Zone7, 0),

         MATCH(IF(AND($[Pack1]$9 > 0, $[Pack1]$9 < 6), $[Pack1]$1, IF(AND($[Pack1]$9 > 5, $[Pack1]$9 < 16), $[Pack2]$1, IF(AND($[Pack1]$9 > 15, $[Pack1]$9 < 26), $[Pack3]$1, IF(AND($[Pack1]$9 > 25, $[Pack1]$9 < 51), $[Pack4]$1, IF(AND($[Pack1]$9 > 50, $[Pack1]$9 < 101), $[Pack5]$1, IF(AND($[Pack1]$9 > 100, $[Pack1]$9 < 50000), $[Pack6]$1)))))), Zone1:[Pack6]1, 0))

 

The answer to this formula is 350

Would this be useful to you?

 

Paul Newcome

This solution can be simplified some, but I am trying to allow for as much flexibility as possible to include your number ranges across the top.

 

I am going to use the following for the column names from left to right for the table:

[Table District], [Group 1], [Group 2], [Group 3], [Group 4], [Group 5], [Group 6]

I am also going to assume that there are only 6 rows in your table as that is what you have provided. If you have additional columns or rows in your table, adjust the references accordingly.

 

And for the columns where you enter the data such as District B and 23:

District, Count

 

And I will refer to the column that the formula goes into as:

Formula

.

The first step is to insert a helper row anywhere (I'll use row 1 for this example).

in [Group 1]1, enter the following:

=VALUE(LEFT([Group 1]1, FIND("-", [Group 1]1) - 1))

 

and then dragfill this across the rest of the group columns. This will populate those columns in that row with the low end of your ranges and have them converted to numerical values.

.

Next we will go to the first row in the formula column and enter this:

=INDEX([Group 1]$3:[Group 6]$9, MATCH(District@row, [Table District]$3:[Table District]$9, 0), MATCH(MAX(COLLECT([Group 1]$1:[Group 6]$1, [Group 1]$1:[Group 6]$1, <= Count@row)), [Group 1]$1:[Group 6]$1, 0))

.

You can then dragfill this down the formula column, and your result will be pulled from the table.

.

Here's a more detailed breakdown of what we are doing here in case anyone was interested...

 

First, we know that we want to pull data using a specific row and column reference. That's where we get the INDEX function from.

 

=INDEX(table_to_pull_from, row_number, column_number)

 

The first part of the INDEX function is the easy part. What is our table? In the above example we had [Group 1]3 in the top left and [Group 6]9 in the bottom right., so our table reference would be

[Group 1]3:[Group 6]9

In the example above, you will notice that I used the $ symbol to lock in the row references. This allows us to dragfill the formula down the Formula column while still maintaining the correct cell references for the table. I also used this in other places within the formula for the same reason.

.

Ok. So now we have 

 

=INDEX([Group 1]$3:[Group 6]$9, row_number, column_number)

.

Next we need a row number. That's where the MATCH function comes in. It provides a number based on where within a grid specific data is found.

 

=MATCH(data_to_search_for, where_to_search, match_type)

 

Note: I will always use 0 (zero) for the match type because that looks for an exact match and provides the most accurate results.

 

We have the District Names listed one in each row, so we will use this data point to establish our row number.

 

=MATCH(District@row, where_to_search, match_type)

 

We tell the MATCH function to look in the [Table District] column for our District@row reference and use an exact match for our match type.

 

=MATCH(District@row, [Table District]$3:[Table District]$9, 0)

.

Now that we know which row we are looking for, we can drop this into the second part of the INDEX function.

=INDEX([Group 1]$3:[Group 6]$9, MATCH(District@row, [Table District]$3:[Table District]$9, 0), column_number)

.

All that's left now is the column number. This is where it gets a little tricky and that helper row comes into play.

 

The easiest way for me to explain what we are about to do is this:

 

We are going to look at the low end of each range, and pull the highest number that is less than or equal to our Count. I hope that makes sense...

This is where the helper row comes in. We established the low end of each range as a numerical value.

 

Now we are going to COLLECT all of the numbers in that helper row that are less than or equal to our Count@row, and find the highest number or MAX.

 

=MAX(group_of_numbers_to_pull_the_highest_one_from)

=COLLECT(range_to_collect, criteria_range_1, criteria_1)

 

We are going to start with the COLLECT function. We know we want to COLLECT the numbers from our helper row under the criteria of the numbers in the helper row being less than or equal to Count@row.

 

Once we COLLECT all of the numbers matching that criteria, we want to find the MAX, so we just wrap it in the MAX function like so:

 

=MAX(COLLECT([Group 1]$1:[Group 6]$1, [Group 1]$1:[Group 6]$1, <= Count@row))

 

This MAX/COLLECT (using your example above) would return the value of 16 because that is the highest number (of the low end of each range) that is still less than or equal to our Count@row.

.

Now we have a data point that we can use in another MATCH function to give us a column number for our INDEX function, so let's go ahead and drop this MAX/COLLECT formula into the first portion.

 

=MATCH(MAX(COLLECT([Group 1]$1:[Group 6]$1, [Group 1]$1:[Group 6]$1, <= Count@row)), where_to_search, match_type)

.

Where are we searching for this low end? In our helper row across the Group columns, and of course a zero for an exact match.

 

=MATCH(MAX(COLLECT([Group 1]$1:[Group 6]$1, [Group 1]$1:[Group 6]$1, <= Count@row)), [Group 1]$1:[Group 6]$1, 0)

.

Now we have our column number for our INDEX function, so we can go ahead and drop the above into the final portion.

 

=INDEX([Group 1]$3:[Group 6]$9, MATCH(District@row, [Table District]$3:[Table District]$9, 0), MATCH(MAX(COLLECT([Group 1]$1:[Group 6]$1, [Group 1]$1:[Group 6]$1, <= Count@row)), [Group 1]$1:[Group 6]$1, 0))

.

And there you have it. Let me know if this will work for you or if you need any further explanations.

Chak Khiam

Elegant way of constructing the formula. Working nicely. Thank you.

al@ki

Thank you! This was helpful!

al@ki

This worked perfectly and certainly allowed me to expand the table to our needs. Thank you!

Paul Newcome

Happy to help! 

 

I always try to work in as much flexibility as possible.