How to get value from a sheet to another sheet with a criteria in Google Sheets?

Question

I have one Google sheet table with two sheets.

First one (Sheets 3)

enter image description here

Second One (Shhet 4)

enter image description here

I need to write a formula to fill the C column in Sheet 4 taking values from Sheet 3. Now I set it right values for better understanding my question, but it has ti be done by formula.

Answer 1

You need XLOOKUP() function. Try-

=IF(D2="","",XLOOKUP(D2,B:B,A:A,""))

To refer other sheet, just use sheet name in front of range.

=IF(D2="","",XLOOKUP(D2,'Sheet 3'!B:B,'Sheet 3'A:A,""))

For dynamic spill array, use MAP() or BYROW() function. Try-

=MAP(D2:D12,LAMBDA(x,IF(x="","",XLOOKUP(x,B:B,A:A,""))))