Getting the value of matching columns in WPS spreadsheet

Question

I have two spreadsheets where I maintain Item SubCategory and Item details.

What I want is to add SubCategoryId to the Item table if the CategoryId and the SubCategoryName in the Item table match the CategoryId and the SubCategoryName in SubCategory Table. Hope it makes sense. How do I achieve this? Thank you.

Answer 1

You can use SUMPRODUCT standard or INDEX/MATCH array formulas:

=SUMPRODUCT((A11=$C$4:$C$6)*(D11=$B$4:$B$6)*($A$4:$A$6))

or

{=INDEX($A$4:$A$6;MATCH(1;(A11=$C$4:$C$6)*(D11=$B$4:$B$6);0);0)}

Array formula after editing is confirmed by pressing ctrl + shift + enter