Combination
Two-way lookup with one XLOOKUP and TOCOL
Find the value where a row label and a column header meet, by turning both conditions into one grid of 0s and 1s and flattening it with TOCOL.
Functions used:XLOOKUPTOCOLExcel 2024 or later (This combination needs Excel 2024 or later)
Worked example
Movement of one account in one period
Scenario
For the analytical review of Veldhuis Installatie BV you have the monthly movements per ledger account for periods 10 to 12, from the XAF auditfile. You want a cell where you type an account and a period and see the movement, without counting rows or columns.
The data
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Account | P10 | P11 | P12 | Account | 45004500 | |||
| 2 | 11001100 | €48,100.00€ 48.100,00 | €61,250.00€ 61.250,00 | €70,300.00€ 70.300,00 | Period | P11 | |||
| 3 | 13001300 | €22,500.00€ 22.500,00 | €19,800.00€ 19.800,00 | €26,400.00€ 26.400,00 | Movement | €13,100.00€ 13.100,00=XLOOKUP(1, TOCOL((H2=B1:D1)*(H1=A2:A6)), TOCOL(B2:D6), "Not found", 0)=XLOOKUP(1; TOCOL((H2=B1:D1)*(H1=A2:A6)); TOCOL(B2:D6); "Not found"; 0) | |||
| 4 | 40004000 | €38,200.00€ 38.200,00 | €38,900.00€ 38.900,00 | €54,600.00€ 54.600,00 | |||||
| 5 | 45004500 | €12,500.00€ 12.500,00 | €13,100.00€ 13.100,00 | €18,750.00€ 18.750,00 | |||||
| 6 | 80008000 | €131,900.00€ 131.900,00 | €128,300.00€ 128.300,00 | €164,800.00€ 164.800,00 | |||||
| 7 |
Swipe sideways to see all columns
Formula in H3
=XLOOKUP(1,TOCOL((H2=B1:D1)*(H1=A2:A6)), TOCOL(B2:D6), "Not found", 0)
=XLOOKUP(1;TOCOL((H2=B1:D1)*(H1=A2:A6)); TOCOL(B2:D6); "Not found"; 0)
Result: €13,100.00€ 13.100,00
How the pieces fit together
Watch out: Both TOCOLs must read in the same direction, or the positions no longer line up and you get an amount from a different cell without any warning.
The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.
=H2=B1:D1
=H2=B1:D1
ReferenceInput cell: P11Press Escape to close=Equal toPress Escape to closeReferenceHeader row, 3 cellsPress Escape to closeCompares the period in
H2with each column header. The result is one row: FALSE, TRUE, FALSE. OnlyP11matches.=H1=A2:A6
=H1=A2:A6
ReferenceInput cell: 4500Press Escape to close=Equal toPress Escape to closeReferenceAccount column, 5 cellsPress Escape to closeCompares the account in
H1with each row label. The result is one column of five values, with TRUE only in the fourth place (account 4500).=(H2=B1:D1)*(H1=A2:A6)
=(H2=B1:D1)*(H1=A2:A6)
ReferenceInput cell: P11Press Escape to close=Equal toPress Escape to closeReferenceHeader row, 3 cellsPress Escape to close*MultipliesPress Escape to closeReferenceInput cell: 4500Press Escape to close=Equal toPress Escape to closeReferenceAccount column, 5 cellsPress Escape to closeMultiplying a row by a column gives a grid of the same size as the amounts: 5 rows by 3 columns. TRUE counts as 1 and FALSE as 0, so the grid holds a single 1, exactly where account and period meet.
=TOCOL((H2=B1:D1)*(H1=A2:A6))
=TOCOL((H2=B1:D1)*(H1=A2:A6))
arrayThe range or array to flatten into one column.Input cell: P11Press Escape to close=Equal toPress Escape to closeReferenceHeader row, 3 cellsPress Escape to close*MultipliesPress Escape to closeReferenceInput cell: 4500Press Escape to close=Equal toPress Escape to closeReferenceAccount column, 5 cellsPress Escape to closeTOCOL reads the grid row by row and stacks it into one column of 15 values. The 1 lands in position 11: three values per row, three full rows before it, then the second value of row four.
=TOCOL(B2:D6)
=TOCOL(B2:D6)
arrayThe range or array to flatten into one column.5 rows by 3 columnsPress Escape to closeThe amounts flattened in the same order, row by row. Position 11 holds 13,100, the
P11movement of account 4500.=XLOOKUP(1,
TOCOL((H2=B1:D1)*(H1=A2:A6)), TOCOL(B2:D6), "Not found", 0) =XLOOKUP(1;
TOCOL((H2=B1:D1)*(H1=A2:A6)); TOCOL(B2:D6); "Not found"; 0) XLOOKUP X.ZOEKENFinds a value and returns the matching item from another range.Read more about XLOOKUPPress Escape to closelookup_valueThe value you are looking for.Press Escape to closeTOCOL NAAR.KOLOMFlattens a range into one column.Used here as: lookup_arrayThe one column or row to search in.Read more about TOCOLPress Escape to closearrayThe range or array to flatten into one column.Input cell: P11Press Escape to close=Equal toPress Escape to closeReferenceHeader row, 3 cellsPress Escape to close*MultipliesPress Escape to closeReferenceInput cell: 4500Press Escape to close=Equal toPress Escape to closeReferenceAccount column, 5 cellsPress Escape to closeTOCOL NAAR.KOLOMFlattens a range into one column.Used here as: return_arrayThe range the answer comes from.Read more about TOCOLPress Escape to closearrayThe range or array to flatten into one column.5 rows by 3 columnsPress Escape to closeif_not_found (optional)What to show when nothing matches.Press Escape to closematch_mode (optional)Exact, next smaller, next larger, wildcard or regex.0: exact match (default)Press Escape to closeXLOOKUP searches the flattened grid for the 1 and returns the amount at the same position. If no row and column match, it shows the not-found text instead of #N/A.
Step by step
- H2=B1:D1 compares
P11with the headersP10,P11andP12: FALSE, TRUE, FALSE. - H1=A2:A6 compares 4500 with the accounts 1100, 1300, 4000, 4500 and 8000: FALSE, FALSE, FALSE, TRUE, FALSE.
- Multiplying the row of three by the column of five gives a 5 by 3 grid. Every cell is 0, except row 4, column 2, which is 1 times 1 = 1.
- TOCOL stacks the grid row by row into 15 values. The 1 is at position (4 - 1) x 3 + 2 = 11.
- TOCOL(B2:D6) stacks the amounts the same way: 48,10048.100, 61,25061.250, 70,30070.300, 22,50022.500, and so on. Position 11 is 13,10013.100.
- XLOOKUP finds the 1 at position 11 and returns the amount at position 11: 13,10013.100.
Why it works
The two comparisons describe the row and the column separately; multiplying them combines both into one grid where 1 means both conditions hold. Because both TOCOLs read row by row, position 11 in the grid of 0s and 1s and position 11 in the amounts point at the same cell. XLOOKUP then does an ordinary one-column lookup.
Watch out for
- Both TOCOLs must read in the same direction. Add scan_by_column TRUE to only one of them and the positions no longer line up.
- If
H1holds the account as text ("4500" pasted from a report) and column A holds numbers, no row matches and you see the not-found text. Make both sides the same type. - If an account appears twice, the grid holds two 1s and XLOOKUP returns the first one. On a trial balance extract that is a sign the export needs checking.
Variations
=XLOOKUP(H1,A2:A6, XLOOKUP(H2, B1:D1, B2:D6))
=XLOOKUP(H1;A2:A6; XLOOKUP(H2; B1:D1; B2:D6))
Nested XLOOKUP: the inner one returns the whole P11 column, the outer one picks the row for account 4500. Same result, often quicker to read when there is one key per side.
=INDEX(B2:D6,XMATCH(H1, A2:A6), XMATCH(H2, B1:D1))
=INDEX(B2:D6;XMATCH(H1; A2:A6); XMATCH(H2; B1:D1))
INDEX with two XMATCH positions: row 4, column 2. The classic two-way lookup.
=SUM((H2=B1:D1)*(H1=A2:A6)*B2:D6)
=SUM((H2=B1:D1)*(H1=A2:A6)*B2:D6)
The same grid times the amounts, then summed. It also gives 13,10013.100, but it adds up duplicates instead of taking the first, and it only works for numbers.
Related combinations
Functions used