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

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

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

# Re: Looking up a Horizontal and Vertical value to find a total amount

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

XMATCH function (microsoft.com)

# Re: Looking up a Horizontal and Vertical value to find a total amount

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.