August 24, 2016

Hi Mynda,

I tried to write a formula using If AND for my worksheet, but it seems only working for first row, and error if I scroll down or right.

'IF(AND(Sheet2!A2=Sheet1!$A2,Sheet2!B2=Sheet1!B$1),Sheet2!C2&" "&Sheet2!D2,"NIL")

Would you please let me know my mistakes.

Thank you.

Members

Dashboards

Power Query

Power Pivot

Power BI

December 7, 2016

Hello Estella,

I changed your formula in cell Sheet1!B2 a bit, locked either the column or row reference and then copied it over the remaining cells.

=IF(AND(Sheet2!$A2=Sheet1!$A2;Sheet2!$B2=Sheet1!B$1);Sheet2!$C2&" "&Sheet2!$D2;"NIL")

As you have set the workbook to manual calculation you need to press F9 after you have copied the formula to the remaining cells.

See attached file for reference.

Br,

Anders

August 24, 2016

Hi Anders,

Thank you for your help.

In fact I am looking at the result "customer" & "Free Goods" appear at each of the sales month as long as it meet or fall under the criteria "model" & " sales month". So I rewrite it as below.

IF(AND(Sheet2!$A2:$A13=Sheet1!$A2,Sheet2!$B2:$B13=Sheet1!B$1),Sheet2!$C2&" "&Sheet2!$D2,"NIL")

I am not sure whether this is the right formula to use if I want to see this result. would you please advise.

Thank you.

Estella

Members

Dashboards

Power Query

Power Pivot

Power BI

December 7, 2016

Hello Estella,

First things first, the way the current formula is built we need to have the same amount of rows as the lookup range has, so by extending the number of rows and copy the formula to the new blank cells you will get a result.

In order to get what you want we need to dig a bit deeper in how to build the formula, we need to use INDEX and MATCH plus finish the formula by holding down both SHIFT and CTRL keys before we press the ENTER key, we need to build an array formula.

The formula would be like below.

{=IFNA(INDEX(Sheet2!$A$2:$D$13,MATCH(1,(Sheet2!$A$2:$A$13=Sheet1!$A19)*(Sheet2!$B$2:$B$13=Sheet1!B$18),0),3)&" "&INDEX(Sheet2!$A$2:$D$13,MATCH(1,(Sheet2!$A$2:$A$13=Sheet1!$A19)*(Sheet2!$B$2:$B$13=Sheet1!B$18),0),4),"NIL")}

See attached file for an example for both scenarios.

Now I assume you want to understand more of how the array formula works. For that I believe there is a suitable course here at My Online Training Hub, but I will do an attempt to explain it.

As you see there are two INDEX functions, this is because you want to have a combined result from two different columns but the INDEX function can only give you the result from the intersection of a range, it can only give us one value.

So the first INDEX function get the result from column 3 (Customer)

INDEX(Sheet2!$A$2:$D$13,MATCH(1,(Sheet2!$A$2:$A$13=Sheet1!$A19)*(Sheet2!$B$2:$B$13=Sheet1!B$18),0),3)

and the second from column 4 (Free Goods)

INDEX(Sheet2!$A$2:$D$13,MATCH(1,(Sheet2!$A$2:$A$13=Sheet1!$A19)*(Sheet2!$B$2:$B$13=Sheet1!B$18),0),4)

To break this down a bit further.

The INDEX function has two methods, the array form or reference form. Here I use the array form and it looks like INDEX(array, row_num, [column_num]).

As you see in below sample I have colour coded the sections, so you can see what is what.

INDEX(Sheet2!$A$2:$D$13,MATCH(1,(Sheet2!$A$2:$A$13=Sheet1!$A19)*(Sheet2!$B$2:$B$13=Sheet1!B$18),0),3)

As there are two criterias to find the correct row, we need to use MATCH function. More about how to use INDEX and MATCH functions is found here.

MATCH syntax is MATCH(lookup_value, lookup_array, [match_type]).

MATCH(1,(Sheet2!$A$2:$A$13=Sheet1!$A19)*(Sheet2!$B$2:$B$13=Sheet1!B$18),0)

We instruct here MATCH to look for the value of 1, just because the result of the criterias lookup will result in a TRUE or FALSE value, that is 1 or 0. As we are looking for a row where the two different criterias are true, MATCH will give the value of 1 where the lookup in the range is true.

As we will get a #N/A value where we don't get any value, we need to enclose this with the IFNA() function, here we can type in that instead of #N/A value we want to have NIL.

As we have used arrays to look for the values instead of specific lookup value we need to finish this formula by holding down CTRL+SHIFT and then press ENTER. Otherwise we will get a #VALUE! error.

I hope I have not messed things up, but if so I trust that Mynda or anyone else in this excellent team will correct me.

Br,

Anders

Answers Post

Most Users Ever Online: 57

Currently Online:

12 Guest(s)

Currently Browsing this Page:

1 Guest(s)

Top Posters:

SunnyKow: 651

Frans Visser: 210

David_Ng: 96

mey tithveasna: 71

A.Maurizio: 60

rathanak: 58

yhooithin05: 54

Anders Sehlstedt: 47

julian: 46

PaulFogel: 37

Newest Members:

Stephen Motte

Rodney Hoyle

michael pitre

Samantha Burleson

Krishnaa Kurugod

David McSwain

Shameem Nassiree

Alanna Henderson

Janel Oz

Mohamed Rehan Memon

Forum Stats:

Groups: 2

Forums: 18

Topics: 935

Posts: 4405

Member Stats:

Guest Posters: 1

Members: 42350

Moderators: 1

Admins: 3

Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea

Moderators: Genevieve Tupas