Check multiple columns and return to cell from other multiple columns-CodePudding

So I've added a SS of what i'm trying to do.

I need to check if Value(Col B) is Value3 and Cat (Col C) is Cat4 than fill corresponding cell (K5) under Cat/Vol table with Result (Number). In this case K5 should be filled with Result1(13).
Whenever a new row added under Incoming Data Table, Cat/Vol table should also be updated with the new Result Number. So incoming data table is dynamic and will be updated with rows only.
For instance if B11 is Value2 and C11 is Cat5 than "I4" should be updated with the new Result and Number.

I tried placing IF and IF AND REGEXTRACT formulas under each cell of Cat/Vol Table without anyluck. Thanks in advance.

CodePudding user response：

My answer is not the most elegant but it was quick. The heart of this formula is vlookup that searches using keys of 2 merged cells:

=ifna(ArrayFormula(vlookup($G2&H$1,{arrayformula($B$2:$B&$C$2:$C),$D$2:$E},2,false))
&"("&ArrayFormula(vlookup($G2&H$1,{arrayformula($B$2:$B&$C$2:$C),$D$2:$E},3,false))&")")

It's then copied to all the cells of new table.

Working solution is here: https://docs.google.com/spreadsheets/d/1qbIg9nNfaOwQtSbk8R8-NkT_6cOy3AKr3Xafm72SsNI/copy