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

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

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

hi Anders,

Thank you for your help. It is great to know more formulas in excel to ease my work.

I will definitely look up more courses in how to use these 2 and also array formula.

 

Thank you.

Estella