Pull sub array from array using multiple column_num values

I have a table of data in Worksheet2 that spans from A1:H2024. In Worksheet1, I need to pull all data from the first three columns in the table in Worksheet1, so that I end up with A1:C2024. Is there an easy way to do this?

The table in Worksheet2 has been saved as named range Table1.

I've used the N(INDEX method before to use multiple row or column values in the INDEX function. So I tried this: =INDEX(Table1,0, N(INDEX({1,2,3},,))). This has worked before with other formulas. But in this case, I can't get it to work. For so,go reason it only pulls columns 1, 2, and 3 of just row 1, leaving me with the range A1:C1. Does anyone know what I'm doing wrong?

I found a work around, by using this formula: =A1:INDEX(Table1, 2024, 3)), but it sleeps slightly less efficient and I was wondering if there's a better way. Thanks!

Yes, here is an example. I included a few examples of what I've tried so far, with no success, and at the end I included what my final goal should look like. Thanks!

It is not clear to me (and I suspect Oeldere too) exactly what are your expected results. Please complete a sample output sheet rather than just have comments such as "returns array of all rows in column 1".

The end result is that I want to be able to specify a column number (4 in this case) and return a range of all rows, columns 1 thru 4. So A2:D10. In one of my example formulas, I specify all the columns I want to pull (1,2,3,4), and it pulls from all 4 columns, but just from row 1.

Try this in B2 and copy across and down


=IFERROR(INDEX(Worksheet2!$A$2:$D$10,,IF(COLUMN()-1<=$B$1,COLUMN()-1)),"")

My apologies for being vague. Here is a new example sheet that will explain things much better. My end outcome is to have one formula look at the string of text in column B, check if any of the values in Worksheet2 A2:D11 are in the string, and if they are, return the correct spelling (from Worksheet 2 A2:A11). I came up with a long formula that does this, but need something more elegant.