2 way lookup using xlookup

[LegoMason]

I'm trying to reorganize the salary register (system generated) into a table I can more easily use. I want to use xlookup for a 2 way lookup. i need to lookup by title in column b and by department in row 4. my formula is giving a value error.

 

[pecoflyer]

Does this help ?

 

[ETAF]

not sure about xlookup - need to look at that - but i have used Index/match which i have used for grid lookup for years

=INDEX('Salary Register'!$B$2:$BJ$64,MATCH('Payroll Input'!C$4,'Salary Register'!$A$2:$A$64,0),MATCH('Payroll Input'!$B6,'Salary Register'!$B$1:$BJ$1,0))


to get rid of the n/a errors - i just wanted to show nomatch , so you can see if there are any issues
try
IFERROR( INDEX('Salary Register'!$B$2:$BJ$64,MATCH('Payroll Input'!C$4,'Salary Register'!$A$2:$A$64,0),MATCH('Payroll Input'!$B6,'Salary Register'!$B$1:$BJ$1,0)) , "")
or if you want 0
IFERROR( INDEX('Salary Register'!$B$2:$BJ$64,MATCH('Payroll Input'!C$4,'Salary Register'!$A$2:$A$64,0),MATCH('Payroll Input'!$B6,'Salary Register'!$B$1:$BJ$1,0)) , 0)


had a play with xlookup
try this
=XLOOKUP('Payroll Input'!C$4,'Salary Register'!$A$2:$A$64,XLOOKUP('Payroll Input'!$B6,'Salary Register'!$B$1:$BJ$1,'Salary Register'!$B$2:$BJ$64))

 

[LegoMason]

perfect!, thank you both!

 

[Peter Bartholomew]

Much as I like XLOOKUP, I believe XMATCH offers a better solution for a2-way lookup because it will return the result as a single2D array.

= LET(
    row,     XMATCH(dept,     TAKE(register,,1)),
    column,  XMATCH(category, TAKE(register, 1)),
    IFNA(INDEX(register, row, column), "")
  )

Dressing it up a bit more and you can include the totals in the single array formula by using



The catch might be that SUBTOTALS do not fit well within an array formula.