How to find the values of diagonal cells in a table in Excel

Today I'm going to show you how to use the OFFSET function combined with Index and Match to find the values of diagonal cells in a table using my Super Bowl squares template as example.

Use the OFFSET function to find the value of a cell to the upper left, lower left, upper right, or bottom right of another cell. Let's say my name is in cell B2, the reference cell. To go up a row use -1, to go down a row use 1. Use -1 to go to a column to the left, +1 to go to a column to the right.

Upper left: =OFFSET(B2,-1,-1)
Upper right: =OFFSET(B2,-1,1)
Lower left: =OFFSET(B2, 1,-1)
Lower right: =OFFSET(B2, 1,1)

Now this is useful if you know the exact location of the reference cell, but what if that reference cell is always moving? That's where INDEX and MATCH come into play. A perfect example is taken from my Super Bowl Squares spreadsheet template. Watch the video below to see how I can get the values of the diagonal cells when the reference cell can change once I hit the randomize numbers button:

What do you think of that? Would you like to see more examples like this, taken straight out of a template? Let me know in the comments below.

Advanced Custom HLOOKUP Formula

About a year ago I posted an explanation how to create an advanced custom vlookup formula. Recently, I had a reader ask me how to convert this custom code into an advanced custom hlookup formula. It's not as easy as changing all the columns to rows and vice versa as the offset function needs to also be applied. Here is the custom hlookup VBA code:

Public Function HlookupNth(MyVal As Variant, MyRange As Range, Optional RowRef As Long, Optional Nth As Long = 1)
Dim Count, i As Long, cll As Range
Count = 0
If RowRef = 0 Then RowRef = MyRange.Rows.Count
For Each cll In MyRange.Rows(1).Cells
  If cll.Value = MyVal Then
    Count = Count + 1
    If Count = Nth Then
      HlookupNth = cll.Offset(RowRef - 1).Value
      Exit Function
    End If
  End If
Next cll
HlookupNth = "Not Found"
End Function

And the forumla would have this format:

=HLOOKUPNTH(lookup_value, lookup_Range, col_index_num, nth_value) 


And a reminder: don't forget to get your copy of our Super Bowl squares spreadsheet before the big game on Sunday.

How do you transpose a column into a row with a formula in Excel?

It's easy to copy and paste data to turn vertical columns into horizontal rows. But sometimes you may want to automate this task. You can use array formulas to transpose lists of data.  Transposing a range means turning a row into a column, and turning a column into a row.  In other words, it rotates the orientation of the data by plus or minus 90 degrees. You can reverse the order or keep it the same.Here's how to do both:


How to transpose a column Into a row with the same order:

These functions will transpose a column list, CList, into a row.  To keep the data in the original order, create a named range called RList referring to the cells in a row that is to contain the transposed data.  Then use the following array formula: 


=OFFSET(CList,COLUMN()-MIN(COLUMN(RList)),0)

How to transpose a column Into a row with the REVERSE order:

To reverse the order, create the named range "RevRList" then enter this formula:

=OFFSET(CList,MAX(COLUMN(RevRList))-COLUMN(),0) 

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007?

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007? The formulas you will need use are the INT, MOD, COUNTA, ROW, and OFFSET functions. Here is an explanation of each:

INT rounds a number down to the nearest integer.
MOD Returns the remainder from division: MOD(number,divisor).
COUNTA function counts the number of cells that are not empty in a range.
ROW(reference) is the cell or range of cells for which you want the row number.
Row ( ) If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.
Example: =ROW( ) - 1 inserted into a cell in row 26 will yield the result 25.


Here are the formulas for a two column combination:
=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)
=OFFSET($A$1,INT(MOD((ROW()-1),COUNTA(B:B))),1)

First Column Formula Explanation:

=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)

Takes the row number and subtracts one from it. Then it divides this number by the number of options in the second column. This value is then rounded down to the nearest integer (whole number). This in turn is the number of the row in which it will select the answer, starting with 0.




Later on I will give an example of to use this formula up to ten unique columns.