Reverse Lookup in Excel and Power Query

The source data is presented in a cross-tabulated layout, featuring Teams as row headers and Regions as column headers. The table contains Manager IDs within its cells.

Our goal is to determine the Team and Region associated with a specific ID.

In this post, I will showcase several techniques using Excel and Power Query to achieve this.

Reverse Lookup.xlsx

Download XLSX • 19KB

Excel formulas are in C9 cell,

Method-1: Suggested by Hagia Sofia.

=TEXTSPLIT(CONCAT(IF(C3:F6=C8,B3:B6&"|"&C2:F2,"")),,"|")

Method-2:

=LET(s,SUMPRODUCT,e,SEQUENCE,v,VSTACK,a,B2:F6,INDEX(a,v(s((a=C8)*e(ROWS(a))),1),v(1,s((a=C8)*e(,COLUMNS(a))))))

Method-3:

=LET(s,SUMPRODUCT,e,SEQUENCE,i,INDEX,a,C3:F6,VSTACK(i(B3:B6,s((a=C8)*e(ROWS(a)))),i(C2:F2,s((a=C8)*e(,COLUMNS(a))))))

Method-4:

=LET(s,SEQUENCE,v,VSTACK,a,B2:F6,b,TOCOL(IFS(a=C8,s(ROWS(a))*10^2+s(,COLUMNS(a))),3),INDEX(a,v(INT(b%),1),v(1,MOD(b,100))))

Method-5:

=LET(a,C3:F6,l,LAMBDA(x,TOCOL(IFS(a>"",x))),TOCOL(FILTER(HSTACK(l(B3:B6),l(C2:F2)),l(a)=C8)))

Method-6:

let

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

 ID = "F7016",

 Result = Table.FromList(List.FirstN(Record.ToList(Table.SelectRows(Table.UnpivotOtherColumns(Source,{"Team/Region"},"A","V"), (f) => f[V] = ID){0}),2), null, {"Result"})

in

 Result

Method-7:

let

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

 ID = "F7016",

 Result = Table.FromColumns({List.FirstN(List.Select(Table.ToRows(Table.UnpivotOtherColumns(Source,{"Team/Region"},"A","V")), (f) => List.Last(f) = ID){0},2)},{"Result"})

in

 Result

Method-8:

let

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

 ID = "F7016",

 Result = Table.FromValue(List.FirstN(List.Intersect({Table.ToRows(Table.UnpivotOtherColumns(Source,{"Team/Region"},"A","V")),{{ID}}}, each List.Last(_)){0},2),[DefaultColumnName = "Result"])

in

 Result