Return value with VLOOKUP that has three search criteria - two values and one blank cell

Question

Based on the Columns A AND B, (which are duplicates), I'd like to return the value in Column D IF Column C is blank

Answer 1

Consider adding these two columns!

ColE: It will join both columns and B (in E2)...

=ARRAYFORMULA(CONCAT(A2:A,B2:B))

ColF: ... in order to count them and find the duplicates in (F2)

=byrow(E2:E,lambda(each,IF(each="","",COUNTIF(E2:E,each))))

ColH: This query will return A,B and D columns where C is blank and it's a duplicate (ColF > 1)

=query(A:F,"SELECT A,B,D where F > 1 AND C is null")

You can see it in action in this sheet: https://docs.google.com/spreadsheets/d/1fu8Z38IVj46fF1uXABZkX9s06vxF-UU9TkO9Bs9KtW4/edit?usp=sharing

I've tried to do it all in one cell, but calculations took forever