Forum Discussion

Copper Contributor

Nov 07, 2022

Solved

Problem with INDEX : returns only first match as a column

Hello, thanks in advance for your insights. I'm trying to generate an array of multiple columns and rows using INDEX but it returns only the values corresponding to the first match row but on a colu...

excel

Formulas and Functions

mtarler
Nov 07, 2022
You said "I did take that into account. Here's the formula I adapted from the one Patrick2788 suggested : =LET(r;LIGNES(Tableau1);INDEX(Tableau1;SEQUENCE(r);{3;1;2;4})) ..."
So did you try the formula with "3,1,2,4" FIRST? or immediately replace the commas with semi-colons? The rows must be in an array perpendicular to the array for columns (i.e. 1 must use commas and the other semi-colons if those are the 2 deliminators).

EDIT: here is a visual example of how rows & columns affect the output of INDEX:

notice how if both input are commas, the output is 3 cells in a row {(1,2),(4,4),(5,3)}
and if both are using semi-colons the output is 3 in a column {(1,2);(4,4);(5,3)}, same pairs as above but in a column instead of a row.
then if the 2 are different and you swap them you end up with the 2 matrix that are transpose of each other.

johroc

Copper Contributor

Nov 07, 2022

HansVogelaar, Here's an image with the table, the formula used and the output. I would expect to obtain an array of 12 rows and 4 columns, but instead I get one column with the information from the first row

Tableau1 :

Category	Name	Product	Level	Cost
FIN	Beltran	Apples	1	85
FIN	Harald	Cherries	2	45
FIN	Hanson	Potatoes	4	32
ADM	Yamaha	Bananas	1	45
ADM	McCormack	Apples	3	85
CSV	Kingston	Bananas	1	255
CSV	Galaed	Cherries	3	21
CSV	German	Apples	2	54
FIN	Tolsky	Cereal	1	28
FIN	Thorton	Turnips	2	1
CSV	Alberto	Turnips	2	65
ADM	Felipe	Potatoes	3	2

Formula : =INDEX(Tableau1;0;{3;1;2;4})

Output :

Apples

FIN

Beltran

Patrick2788

Silver Contributor

Nov 07, 2022

johroc

Try this:

=LET(r,ROWS(tableau1),INDEX(tableau1,SEQUENCE(r),{3,1,2,4}))

johroc
Copper Contributor
Nov 07, 2022
Hi, Patrick2788 Here's the outcome with that formula :

Apples
FIN
Hanson
1
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
#N/A
- PeterBartholomew1
  Silver Contributor
  Nov 07, 2022
  The column indices need to form a row array in the machine locale. That may be
  {3,1,2,4}
  or
  {3;1;2;4}
  according to your computer settings.
  - johroc
    Copper Contributor
    Nov 07, 2022
    I did take that into account. Here's the formula I adapted from the one Patrick2788 suggested : =LET(r;LIGNES(Tableau1);INDEX(Tableau1;SEQUENCE(r);{3;1;2;4})) my system is in french, that's why I changed rows for lignes and commas for semicolons.
- Patrick2788
  Silver Contributor
  Nov 07, 2022
  You may want to switch my commas out for semi-colons.
  - johroc
    Copper Contributor
    Nov 07, 2022
    Patrick2788 , I did. And that was the output I've got. Thank you