Use of Excel formula 'IF" and "AND" with multiple sheet to combine the data-CodePudding

I have two excel datasheets i.e. 'Data' and 'Sheet1'. Now, I am using the following excel formula to bring some data from 'Sheet1' to 'Data':

=IF(AND(Data!B2=Sheet1!A2,Data!C2="Argentina"),Data!AU2:CR2=Sheet1!B2:AY2,NAN)

The formula return "#NAME?".

Can you help solve this issue? Thanks in advance.

CodePudding user response：

You no need to mention destination sheet cells in formula. If function doesn't accept this. Try below formula

=IF(AND(Data!B2=Sheet1!A2,Data!C2="Argentina"),Sheet1!B2,"NAN")

CodePudding user response：

This is advise too large to fit into a comment:

In order to solve such an issue, you can use Formula auditing: it gets into your formula and solves it step by step.

After some evaluations, I get the question to evaluate this formula:

=IF(FALSE, Data!AU2:CR2=Sheet1!B2:AY2, NAN)

And the moment it wants to evaluate Data!AU2:CR2=Sheet1!B2:AY2, I get an error.

I tried to put this formula excerpt as a single formula into a sheet, and as a result I got a complete row, filled with boolean values (which does not fit into one single cell, explaining the problem).

What are you trying to do here? Do you want every single value to be equal? Do you want matches? ...