Today we are going to discuss about numerous methods in Excel functions and Power query M-Code to do 3-way lookup. Lot to learn, let's dive in.

Allow me to guide you through our source data, which resides in the range A5:H17.

It is in cross tabulated format with 'Property' and 'Section' in row headers and 'Month' in column header.

Our requirement is to do 3-way lookup based on the criteria in A2:C2 and find the value in D2 cell.

Consider, selected values of

Property is 'A'

Section is 'S2' and

Month is 'Apr'

Method-1:

Formula in D2 cell is,

=INDEX(C6:H17,MATCH(A2&"|"&B2,A6:A17&"|"&B6:B17,0),MATCH(C2,C5:H5,0))

Let start with MATCH construction in row_num argument of INDEX function.

In lookup_value argument, concatenate both row headers 'Property' and 'Section' with pipe symbol ("|"). If we evaluate this part, output will look like "A|S2".

In lookup_array argument, concatenate both 'Property' column and 'Section' column with pipe symbol ("|"). If we evaluate this part,

{"A|S1";"A|S2";"A|S3";"A|S4";"A|S5";"A|S6";"B|S11";"B|SA";"B|SB";"B|SC";"B|SD";"B|SE"}

In match_type argument 0 is used for exact match.

Then MATCH function will deliver us the relative position of "A|S2" which is 2.

MATCH function in column_num argument is simple and straight forward.

Relative position of 'Apr' in the range C5:H5 is 4.

In the range C6:H17, intersection value of row 2 and column 4 is 351.

Method-2:

=XLOOKUP(C2,C5:H5,XLOOKUP(A2&"|"&B2,A6:A17&"|"&B6:B17,C6:H17))

Inner XLOOKUP helps us to get the matching record of row headers Property 'A' and section 'S2'.

Outer XLOOKUP helps us to pick the value of month 'Apr'.

Method-3:

=FILTER(FILTER(C6:H17,(A6:A17=A2)*(B6:B17=B2)),COUNTIF(C2,C5:H5))

Inner FILTER function helps us to filter matching records of Property 'A' and section 'S2'.

In include argument,

(A6:A17=A2)*(B6:B17=B2)

this construction will deliver us below result,

{0;1;0;0;0;0;0;0;0;0;0;0}

From the range C6:H17 inner FILTER will pick C7:H7 which is,

{168,574,152,351,350,199}

Include argument of outer FILTER will deliver us,

{0,0,0,1,0,0}

Corresponding value of 1 in array {168,574,152,351,350,199} is 351.

Method-4:

Power Query solution,

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Criteria = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

Result = Table.AddColumn( Criteria, "Value", (f) => Record.Field( Table.SelectRows(

Source,each (_[Property] = f[Property]) and (_[Section] = f[Section])){0}, f[Month]))

in

Result

Source and Criteria step extracts both tables into Power Query editor.

in Result step,

Table. AddColumn function adds column in Criteria table in the name of 'Value'.

each is the custom function which does the specified calculation in each row.

Each in Table. AddColumn function refers each row in 'Criteria' table.

Each in Table. SelectRows function refers each row in 'Source' table.

In order to apply filter in Source table, we need to compare 'Property' and 'section' column in both Source and Criteria table.

So, we replaced each in Table. AddColumn function with (f) and GOTO operator =>

f[Property] refers to Property column in Criteria table,

_[Property] refer to Property column in Source table.

Table. SelectRows will deliver us the Source table with one matching row of property 'A' and section 'S2'

Zero inside curly braces {0} converts the table of one row into record.

Record. Field function will help us to pick the value from specified field name.

We fed f[Month] in field name which is 'Apr'. Value in that field is 351.