Index Match Two Sided Array

[AAP]

Hi,
I need help in understanding index match array formula. In the attached file if I use array formula horizontal then it works fine Or I can use vertical (in single column) it works. But cannot be able to use it both sides in an array(means five columns in yellow and down the end rows together). In the attached file I shown how i use it horizontally but if I want to go down and use Ctrl+shft+Enter then it does not give me desired results. Can anyone help me understanding this part.

Kind Regards/Amit

 

[NARAYANK991]

Hi Amit ,

Try this array formula :

=INDEX(Datatable!$B:$F,MATCH(Data!$A2:A401,Datatable!$A:$A,0),{1,2,3,4,5})

Narayan

 

[Ramesh]

Hi,

Not sure, why you are trying to use an array formula.

If you requirement is the determine the column reference and fetch the value from the subsequent row for Country to + customer value, you can fetch the same by substituting the column_num parameter with another match function.

Here is an example:

=INDEX(Datatable!$A$1:$F$401,MATCH($A2,Datatable!$A:$A,0),MATCH(B$1,Datatable!$A$1:$F$1,0))

The above syntax also helps us to introduce new columns in the output table with out modifying the actual formula. You just need to copy/paste the same and it will work.

Thanks,
Ramesh

 

[AAP]

Hi Amit ,

Try this array formula :

=INDEX(Datatable!$B:$F,MATCH(Data!$A2:A401,Datatable!$A:$A,0),{1,2,3,4,5})

Narayan

It worked perfectly, but how do I get curly brackets before and after 1,2,3,4,5?

 

[AAP]

Hi,

Not sure, why you are trying to use an array formula.

If you requirement is the determine the column reference and fetch the value from the subsequent row for Country to + customer value, you can fetch the same by substituting the column_num parameter with another match function.

Here is an example:

=INDEX(Datatable!$A$1:$F$401,MATCH($A2,Datatable!$A:$A,0),MATCH(B$1,Datatable!$A$1:$F$1,0))

The above syntax also helps us to introduce new columns in the output table with out modifying the actual formula. You just need to copy/paste the same and it will work.

Thanks,
Ramesh

Thanks Ramesh, but column names changes frequently so cannot trust to fetch the record thats the reason need hard coded column number.

Regards/Amit

 

[Chihiro]

It worked perfectly, but how do I get curly brackets before and after 1,2,3,4,5?

On keyboard press Shift + [ = {

Usually located just above and to left of Enter key.

 

[AAP]

On keyboard press Shift + [ = {

Usually located just above and to left of Enter key.

Wonderful
Thanks a lot!

 

[AAP]

It worked perfectly, but how do I get curly brackets before and after 1,2,3,4,5?

Thousand thanks Narayan you are Great!