Trying to make comparison between different sheets of data in Google sheets using column pair values-CodePudding

I am trying to get to the point where my Google sheet is going to show up like the picture below.

Basically I am trying to find the common column in different table (sheet) and also make sure the type of the columns matches too. The columns are unsorted and won't be of equal length.

But I am unable to do so using vlookup or query which is becoming to complicated. I am attaching the link for the Google sheet.

Update: Use below formula as dynamic input range from source and target sheet without helper QUERY function.

=MAP(Target!A2:INDEX(Target!A2:A,COUNTA(Target!A2:A)),
LAMBDA(x,IFNA(XLOOKUP(XLOOKUP(x,Source!A2:A,Source!B2:B)=XLOOKUP(x,Target!A2:A,Target!B2:B),
{TRUE,FALSE},{"Match","Type Mismatch"}),"No MAtch")))

CodePudding user response：

I think you have a typo in your Source table (you have 'emd_id' where you meant 'emp_id'). If you correct that, you can delete everything in F7 downwards and try the following in F7:

=arrayformula(ifna(if(sign(match(C7:C13,A7:A10,0))=ifna(sign(match(C7:C13&D7:D13,A7:A10&B7:B10,0))),"Match","Mismatch in type"),"No match"))

We are creating two arrays corresponding to whether the 'column' or 'column' AND 'type' entries match in both Source and Target, then using IF/IFNA to assign the required results based on whether the overall result was TRUE/FALSE/#N/A.