COLUMN() not working properly within INDIRECT() formula

[tygrrboi]

I am running into an issue with a formula working perfectly when I put in the cell reference as text, but not when I am using the COLUMN() formula within it to signify the row I want INDIRECT to point to.

This is my formula:

=IFERROR(TRIM(MID(SUBSTITUTE( INDIRECT("$C$"&COLUMN(B1)), " ", REPT(" ",100)),AGGREGATE(14,6,SEARCH(F2,SUBSTITUTE( INDIRECT("$C$"& "2"), " ", REPT(" ",100)),ROW(INDIRECT("1:"&LEN(SUBSTITUTE( INDIRECT("$C$"&COLUMN(B1)), " ", REPT(" ",100)))))),1)-101, 100)),"")

You can see I have 3 instances of INDIRECT hoping to reference the cell C2. In the first and third spot I can use COLUMN(B1) to get the value of 2 for my indirect formula. (I want to do this because when I slide the formula over to my next column, I want that column to reference C3, hence the columns)

But when I change the second instance in the formula from INDIRECT("$C$" & "2") to INDIRECT("$C$" & COLUMN(B1)) I do not get the result I am looking for.

Is this because the columns formula cannot be used inside SEARCH or AGGREGATE? I do not know why simply changing "2" to a formula that results in 2 as an answer doesn't work.

Can anyone help?

edited: Attached sample file for reference.

[menem]

It's may better if you can attach sample file (include your desired output).

Regards.

[wk9128]

The overall formula does not work, it depends on the data in your worksheet

Can be simplified to, but the Result is different

INDIRECT("$C$" & "2") >>>> =INDIRECT("C2") when drag right , The result will be like this C2,C2,C2 ............

INDIRECT("$C$" & COLUMN(B1)) >>>> =INDIRECT("C"&COLUMN(B$2)) when drag right ,The result will be like this C2,C3,C4 .........

[tygrrboi]

I have edited the post to include the file. Thank you.

[tygrrboi]

The overall formula does not work, it depends on the data in your worksheet

Can be simplified to, but the Result is different

INDIRECT("$C$" & "2") >>>> =INDIRECT("C2") when drag right , The result will be like this C2,C2,C2 ............

INDIRECT("$C$" & COLUMN(B1)) >>>> =INDIRECT("C"&COLUMN(B$2)) when drag right ,The result will be like this C2,C3,C4 .........

Exactly. C2, C3, C4... is my goal... but the formula does not work when I change "C2" to "C"&COLUMN(B$2)

[tygrrboi]

I have broken it down for trouble shooting and have determined the following:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

work fine. They return the same value so the issue is not SEARCH()

Please Login or Register  to view this content.

gives me the correct value, but

Please Login or Register  to view this content.

gives me a #NUM! error
The issue is inside the AGGREGATE() function but I am not sure why.

[menem]

If you don't want to change overall your formula.
Just cover COLUMN with SUM.

=IFERROR(TRIM(MID(SUBSTITUTE( INDIRECT("$C$"&SUM(COLUMN(B1))), " ", REPT(" ",100)),AGGREGATE(14,6,SEARCH(F2,SUBSTITUTE( INDIRECT("$C$"& "2"), " ", REPT(" ",100)),ROW(INDIRECT("1:"&LEN(SUBSTITUTE( INDIRECT("$C$"&SUM(COLUMN(B1)))), " ", REPT(" ",100)))))),1)-101, 100)),"")

Regards.

[tygrrboi]

If you don't want to change overall your formula.
Just cover COLUMN with SUM.

=IFERROR(TRIM(MID(SUBSTITUTE( INDIRECT("$C$"&SUM(COLUMN(B1))), " ", REPT(" ",100)),AGGREGATE(14,6,SEARCH(F2,SUBSTITUTE( INDIRECT("$C$"& "2"), " ", REPT(" ",100)),ROW(INDIRECT("1:"&LEN(SUBSTITUTE( INDIRECT("$C$"&SUM(COLUMN(B1)))), " ", REPT(" ",100)))))),1)-101, 100)),"")

Regards.

This works somehow. Thank you so much! If its OK, would you mind explaining to me why SUM() is needed so I can work it out for future formulas?

[menem]

Don't know exactly.
But if you select COLUMN(B1) then press F9 it will give you a {2} as result. (Answer as array)
While SUM(COLUMN(B1)) give you only 2. Maybe it effect to INDIRECT in someway.

Regards.

[kvsrinivasamurthy]

It is better to use INDEX formula instead of INDIRECT

In G2

Please Login or Register  to view this content.

IN J2

Please Login or Register  to view this content.