Search for one value in a row and then few other values in the same row-CodePudding

I have SheetA and SheetB in Google Spreadsheets.

I'm trying to write a formula that would be for example in Cell B2 of SheetA and would check if corresponding data is entered in SheetB and display person's name from row with corresponding date in cell B2 of SheetA.

It would repeat on other cells, of course, cell B2 is just for example.

So my basic problem is how to make a function that would search following in SheetB: Particular Date in B, and then Name in A and Task in C on the same row as the Particular Date

I hope I'm clear enough.

I've looked into IFS, LOOKUP, MATCH functions but I can't connect the dots and come up with the solution. :(

SheetA

SheetB

CodePudding user response：

try in B2:

=INDEX(IFNA(VLOOKUP(A2:A10&B1:E1, {SheetB!B:B&SheetB!C:C, SheetB!A:A}, 2, 0)))

=INDEX(IFNA(VLOOKUP(A2:A10&B1:E1; {SheetB!B:B&SheetB!C:C\ SheetB!A:A}; 2; 0)))