Using Label Headers as Intersecting Lookup Criteria - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Monday, December 16, 2019

Using Label Headers as Intersecting Lookup Criteria

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))),"")
TET4a
In the next picture, you select your Quarter field name.
TET4b
In the next picture, you select your Region field name.
TET4c
Here is the resulting value at the intersection of those 2 selected criteria.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment