IndexMatch Conditional Col #Ref Error

Greetings,

I thought I had this issue behind me, but I am unhappy with the huge array that Excel has to calculate whenever the sheet recalculates.

Formula:

Please Login or Register  to view this content.

So I figure easy enough I will just move the dataSet closer to the formula & Excel should have less work to do, well it only works as long as I have a Col A ref for the index, if I change it to say AI where the data is now I get a #Ref error. When I breakdown each of the nested formulas to individual ones they work independently. Well that is untill I get to index & try to give it my new array starting in AI.

Formula:

Please Login or Register  to view this content.

So I think the more relevant question from me is how would one do indexMatch with a conditional col ref to pass off to the index.

TIA

So I think the more relevant question from me is how would one do indexMatch with a conditional col ref to pass off to the index.

TIA

I'll hazard a guess and suggest the CHOOSE function.

Perhaps an uploaded representative (sanitized) Excel file would help us to be more specific.

I havent had much practice with the choose function & having a hard time figuring this out. How would I use choose to feed the appropriate col to index. This is what I came-up with but I dont think I am doing it right. It gives me Col 63s & then Blanks instead of the other Col

Formula:

Please Login or Register  to view this content.

I will see if I can gut this thing & make a sample workBook.

Update_I. to this post:

I created a thread just for the choose question I am asking heres a link to it.



Update_II. to this post: Still cleaning-out this workBook to give a sample I hope to be done soon.

Sadly I have made no headway on this heres the SampleDataSet: SmallerArray.xlsx TIA

Hi dlow,

I see your sample workbook and have no idea what problem you are trying to solve. What is the expected outcome?

Without knowing what the final result might be, I run the Formula Evaluate dialog. I see that the next to last Column(AZ3) is giving you this problem. I've stepped through the Evaluate a few times and have no idea what the final answer or goal might be.

My question is, could a Pivot Table or Advanced Filter or other built in function help getting you the address or answers you need, without needing a long formula. Without understanding what these point to, I'm still in the dark.

I'm confused, but does this help??

Formula:

Please Login or Register  to view this content.

The reason you get a #REF! error when you change the formula is that the column reference within INDEX is larger than the number of columns in the [changed] array. For example if you use this formula

=INDEX(D1:J1,1,9)

....you will get an error because 9 is a larger number than the number of columns in D1:J1 (7). You are doing the same here, COLUMN(AZ3)=52 and COLUMN(BO3)=67 but the array used in INDEX is AH$3:$BO$202 which has only 34 columns. The column references are not absolute but relative to the array.

Given that you have the required row number in column AX couldn't you just use that rather than using MATCH to find it again, e.g. this formula in AS3 copied down

=IF(AR3<>"",CELL("address",IF(INDEX($AZ:$AZ,AX3)<>"",INDEX($AZ:$AZ,AX3),INDEX($BO:$BO,AX3))))

use
=IF(AR3<>"",CELL("address",INDEX(AH$3:$BO$202,MATCH(AR3,$AH$3:$AH$202,0),IF(INDIRECT(ADDRESS(AX3,COLUMN(AZ3)))<>"",COLUMN(AZ3)-COLUMN(AH3)+1,COLUMN(BO3)-COLUMN(AH3)+1))),"")

instead of =IF(AR3<>"",CELL("address",INDEX(AH$3:$BO$202,MATCH(AR3,$AH$3:$AH$202,0),IF(INDIRECT(ADDRESS(AX3,COLUMN(AZ3)))<>"",COLUMN(AZ3),COLUMN(BO3)))),"")

thanks for your feedback and reputation

Wow - I still don't have any idea what a "fixed" answer would look like. I did understand DLL's explanation of the last argument being greater than the range of cells giving the bad Ref answer. I'm going to pass next time on questions where I need ask more than once for the type of question this is problem is solving.

I'm very glad you learned something from all the help. I've learned something too.