Forum Discussion
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")
- You could use a double XLOOKUP:
https://exceljet.net/formula/xlookup-two-way-exact-match 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.
Example 3 of this link should be similar to what you are looking for.
XMATCH function (microsoft.com)
