Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria

Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria
I previously posted this example of using the spacebar character as the mathematical operator in a formula to sum numbers in the intersecting range of multiple rows and columns. Also is this other example of using a formula to lookup an intersecting value. Here is a combination of those two methods, using the location of row and column header labels to return their intersecting cell’s value. In the first picture, a worksheet holds data for each region’s quarterly numbers in range B2:F6. Yellow cells H3 and I3 are data validated for lists of Quarter labels in column A, and Region labels in row 1.

The green cells hold the lookup result.
The formula in cell H8 is
=IF(LEN(I8)=0,"","Lookup result:")
The formula in cell I8 is
=IFERROR(INDIRECT(ADDRESS(MATCH(H3,A:A,0),MATCH(I3,1:1,0))),"")

In the next picture, you select your Quarter field name.

In the next picture, you select your Region field name.

Here is the resulting value at the intersection of those 2 selected criteria.