Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Looking up a Horizontal and Vertical value to find a total amount

Copper Contributor

Hey Everyone, 

I am trying to write a function that will look up a horizontal and its corresponding vertical value in the matrix to find a total amount paid in another sheet. 2 criteria's have to be met in order for a number to populate in the matrix. This is what I've came up with thus far, but I keep returning an error. Any help is appreciated!


=IFERROR(INDEX('2022 August'!$B$7:$L$85,XMATCH('2022 August'!A7,Sheet2!$A:$C),XMATCH('2022 August'!$B$6,Sheet2!$A:$C)),"N/A")

3 Replies


Example 3 of this link should be similar to what you are looking for.

XMATCH function (



The formula should probably look like this:


=IFERROR(INDEX('2022 August'!$B$7:$L$85, XMATCH(Sheet2!Cell1, '2022 August'!$A$7:$A$85), XMATCH(Sheet1!Cell2, '2022 August'!$B$6:$L$6)),"N/A")


where Cell1 and Cell2 contain the values to be looked up.