Index match combined

[mari_hitz]

Hi everyone, hope you are good! Please note that I am trying to figure out how a formula is working on a spreadsheet that I have. I understand how Index and match work combined, nonetheless, I can see that before the match formula there is a "1" and I cannot understand how it works, in addition, it appears that there are "*", is this some sort of concatenate for multiple options? Could you please help me to understand how this formula would work?

'INDEX(A_Table[[#All],

:[Years]],(MATCH(1,(A_Table[[#All],[Code]]=[@[Guide]])*(A_Table[[#All],[Group]]=[@[Range]])*(A_Table[[#All],[Years]]<=[@[2018 Years]]),0)),3)

Just to give you a bit of background, this formula in a spreadsheet called Data, then there is another spreadsheet which contains the "A_Table" and "Guide Code", "Years",  "Guide" and "Group" are columns in this table. In addition "Range" and "2018 Years" are columns in the Data spreadsheet. 

I appreciate your help and support in advance. Regards!

 

[Marcelo Branco]

This is a normal way of lookup with various conditions.

The * in the formula means multiplying the various conditions, ie
(condition1) * (condition2) * (condition3)

In Excel: FALSE * FALSE = 0; FALSE * TRUE = 0; TRUE * TRUE = 1

For example
(TRUE; FALSE; TRUE) * (TRUE; FALSE; TRUE) * (FALSE; TRUE; TRUE)

Multiplying conditions 1 and 2
(1;0;1)

And multiplying by the third condition, we obtain
(0;0;1)

At last, in the example,
MATCH (1, {0;0;1}, 0) result = 3

M.

 

[mari_hitz]

Thanks for your reply Marcelo! Can I make a quick question? The above formula returns #N/A, when I click on evaluate to check why the 3 statements are 1,1,0. Does this have an impact and that is why is returning #n/A. It is weird because it should return a the value on column 3 on the other table, is complete and the fields comparable are a match. Thanks!

 

[Marcelo Branco]

It's an array formula so you must confirm the formula with Ctrl+Shift+Enter, not just Enter.

M.