Nov 07 2022 07:58 AM
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 column.
Nov 07 2022 08:07 AM
Please provide more detailed information.
Nov 07 2022 08:22 AM
@Hans Vogelaar, 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 |
1 |
Nov 07 2022 08:50 AM
Nov 07 2022 09:01 AM
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 |
Nov 07 2022 10:34 AM
Nov 07 2022 11:14 AM
Nov 07 2022 11:15 AM
@Patrick2788 , I did. And that was the output I've got. Thank you
Nov 07 2022 11:19 AM
Nov 07 2022 11:22 AM
Nov 07 2022 11:23 AM
Nov 07 2022 11:35 AM - edited Nov 07 2022 11:46 AM
SolutionYou 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.
Nov 07 2022 11:35 AM - edited Nov 07 2022 11:46 AM
SolutionYou 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.