Multiple use of IF and Match in one formula

Question

I have an issue trying to set up a formula which will include 2 ifs and 2 Match inside.

Here is an example:

=ARRAYFORMULA(IFNA(IF(MATCH(B6,'Sheet1'!$G$3:$G,0),"Taken",IF(MATCH(B6,'Sheet2'!$G$3:$G,0),"Damaged", "Available"),"Available"))

Result always shown that Value from B6 not found in second part of the formula which is 2nd IF, I have double checked and Value from B6 is in the range of MATCH(B6,'Sheet2'!$G$3:$G,0)

Any ideas how to solve the issue?

I have tried use IF & IFS but did not work either.

Answer 1

Can you try with:

=ifs(ifna(xmatch(B6,Sheet1!G3:G)),"Taken",ifna(xmatch(B6,Sheet2!G3:G)),"Damaged",1, "Available")