Need help with INDEX formula

Originally Posted by Glenn Kennedy

Just remove confidential infromation first.

Without having a sheet to play with, many helpers will just look at this and move on... Just as I am.

Added the spreadsheet now

How about this?
=SORT(CHOOSECOLS(FILTER(A1:T33,(O1:O33>=N35)*(O1:O33<=N36)),COLUMN(A1),COLUMN(N1),COLUMN(O1),COLUMN(Q1)),4,1)

Originally Posted by Gregb11

How about this?
=SORT(CHOOSECOLS(FILTER(A1:T33,(O1:O33>=N35)*(O1:O33<=N36)),COLUMN(A1),COLUMN(N1),COLUMN(O1),COLUMN(Q1)),4,1)

Thank you! This formula gave me an error. Could you try to implement it in the sheet so I can download it?

Originally Posted by Glenn Kennedy

But you haven't said HOW you want to identify the columns. At a guess:

=LET(A,"AQRT",f,FILTER(A1:W33,(R1:R33>=Q35)*(R1:R33<=Q36)),IF(ISERR(ROWS(f)),"Ingen utbytte",SORT(INDEX(f,SEQUENCE(ROWS(f)),TRANSPOSE(LOOKUP(CODE(+MID(A,SEQUENCE(LEN(A)),1))-64,SEQUENCE(26)))),4)))

Thank you!
If I remove columns, the formula changes. So this is still not working.

In this example, I want to return the values of col A (1), col Q (17), col R (18), col T (20).
The way I have been identifying them now is by column numbers, I can see that will not work if I remove or add columns.
Can you make an example of how to return those 4 columns by identification, for example naming the columns?

Originally Posted by Glenn Kennedy

Did you actually open the file???? Did you look at the formula I used??

I have put same the formula (again) into the file. This time BESIDE your formula (yours shaded green... mine is blue).

=LET(A,"AQRT",f,FILTER(A1:W33,(R1:R33>=Q35)*(R1:R33<=Q36)),IF(ISERR(ROWS(f)),"Ingen utbytte",SORT(INDEX(f,SEQUENCE(ROWS(f)),TRANSPOSE(LOOKUP(CODE(+MID(A,SEQUENCE(LEN(A)),1))-64,SEQUENCE(26)))),4))))

The bit in RED in the formula (above) defines the columns to be returned.

Thank you!

As you can see I have opened the spreadsheet and I can see your code, but it is not working.
Clearly, you can see that after removing column L ( empty column)both of the formulas are not returning the correct columns in the array anymore.

Originally Posted by Gregb11

Sorry about that. I pasted the formula in AFTER i tested it by deleting the columns- oops. This one should work - you can delete columns and it will still show the right info:

=SORT(CHOOSECOLS(FILTER(A1:W33,(R1:R33>=Q35)*(R1:R33<=Q36)),COLUMN(A1),COLUMN(Q1),COLUMN(R1),COLUMN(T1)),4,1)

In the attached, my results are BELOW your results

Thank you!
I have tested this spreadsheet and I can both remove column L and add another one without the numbers failing like my own solution.
Have to test this setup against my main sheet, but comfortable this could be a good solution!

Originally Posted by Glenn Kennedy

You need to specify which columns you want to return in the firdt red bit in the formula i gave you. If it is ALWAYS those 4 columns, use Greg's approach.

That is the problem. I am going to use it in many different places which will be affected of the columns around.
The first part, you have AQRT marked red. How do I specify the columns actually?

Originally Posted by Glenn Kennedy

Just enter the column letters as shown. Type in "BCDE" if you want those columns returned.

If your columns had headers, it would be essier... but they don't.

I think you haven't specified what yiu want, clearly enough.

Let me take a look again, guess I didn't quite understand how you did it until now.
I will have 11 more of this setup to the right. This is only data for january, I will have data for a whole year. So there will be many columns to the right where each month (like this) result in an array below with 4 columns I want information from.
The first column will alway be 1, but the 3 next in every formula will have the same distance as this setup with columns is for 12 different months with data, but the type of data is always found in the same distance from each other.

The problem I got and that's why I requested help here is because I have to add more columns with more data for each month. As I start to do that, all of the 12 formulas stop working and that gives me a load of work.

So is the formula in Post #11 working for you or do you need it to do something else?

Originally Posted by Gregb11

So is the formula in Post #11 working for you or do you need it to do something else?

It is working in this spreadsheet which was a test. Give me a little time to test it out and see how it goes.

Originally Posted by Gregb11

Sorry about that. I pasted the formula in AFTER i tested it by deleting the columns- oops. This one should work - you can delete columns and it will still show the right info:

=SORT(CHOOSECOLS(FILTER(A1:W33,(R1:R33>=Q35)*(R1:R33<=Q36)),COLUMN(A1),COLUMN(Q1),COLUMN(R1),COLUMN(T1)),4,1)

In the attached, my results are BELOW your results

I am looking into this spreadsheet now and need to to ask how you define those 4 columns to be returned?

Not sure I understand, but the Columns returned are defined by this part:
COLUMN(A1),COLUMN(Q1),COLUMN(R1),COLUMN(T1)

This allows for if you delete or insert columns, the reference will also change appropriately.

The reason it didn't work is because you need to keep the range starting with A. So you can do this and it works:

=SORT(CHOOSECOLS(FILTER($A2:AA24,(V2:V24>=U26)*(V2:V24<=U27)),COLUMN(E2),COLUMN(U2),COLUMN(V2),COLUMN(X2)),4,)

Originally Posted by Gregb11

The reason it didn't work is because you need to keep the range starting with A. So you can do this and it works:

=SORT(CHOOSECOLS(FILTER($A2:AA24,(V2:V24>=U26)*(V2:V24<=U27)),COLUMN(E2),COLUMN(U2),COLUMN(V2),COLUMN(X2)),4,)

Thank you, I made it work again

What about the column references, is that possible?

What about the column references, is that possible?

I'm not sure exactly what you mean. Why do you want to do that if this already works? Are you saying you'd want it to search the top row and whenever if finds that text to use that column?

Originally Posted by Gregb11

I'm not sure exactly what you mean. Why do you want to do that if this already works? Are you saying you'd want it to search the top row and whenever if finds that text to use that column?

Yes, exactly. That is for me to have better control because there will be a lot of columns in my final spreadsheet.

Replace the "Column(E2)" in the formula with "MATCH("Tall",A1:W1,0), etc. The A1:W1 is whatever range you're working with.



=SORT(CHOOSECOLS(FILTER(A2:W34,(R2:R34>=Q36)*(R2:R34<=Q37)),MATCH("Tall",A1:W1,0),MATCH("Nummer",A1:W1,0),MATCH("Dato1",A1:W1,0),MATCH("Dato2",A1:W1,0)),4,1)

Originally Posted by Gregb11

Replace the "Column(E2)" in the formula with "MATCH("Tall",A1:W1,0), etc. The A1:W1 is whatever range you're working with.



=SORT(CHOOSECOLS(FILTER(A2:W34,(R2:R34>=Q36)*(R2:R34<=Q37)),MATCH("Tall",A1:W1,0),MATCH("Nummer",A1:W1,0),MATCH("Dato1",A1:W1,0),MATCH("Dato2",A1:W1,0)),4,1)

Thank you very much, will test this out!