IndexMatch Conditional Col #Ref Error

[dlow]

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.

=IF(AV4<>"",CELL("address",INDEX($A$3:$BI$203,MATCH(AV4,$AI$3:$AI$203,0),IF(INDIRECT(ADDRESS(BG4,COLUMN(BI4)))<>"",COLUMN(BI4),COLUMN(AO4)))),"")

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.

=IF(AV4<>"",CELL("address",INDEX($AI$3:$BI$203,MATCH(AV4,$AI$3:$AI$203,0),IF(INDIRECT(ADDRESS(BG4,COLUMN(BI4)))<>"",COLUMN(BI4),COLUMN(AO4)))),"")

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

[FlameRetired]

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.

[dlow]

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

=CHOOSE(INDIRECT(ADDRESS(BJ3,COLUMN(BL3)))<>"",COLUMN(BL3),COLUMN(AO3))

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.

[dlow]

I was thinking about trying choose & forgot all about it. Thanks for reminding me I am gonna try that right now.

[dlow]

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

[MarvinP]

Hi dlow,

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

[dlow]

The simplest way I can think of to describe what I am trying to do is echo back the cell addresses of a list of values in AT & BI. My thought process was that if I knew the row I would just have to find the Col. The row is the date & the Col is whichever of the two that isnt blank.

SideNote:[lol this was the easiest way I could think of to do it, lolz.... Im sure its not the best way. lol I recently looked @ some VBA I wrote a year or two ago & it was over 50 line of code. I crushed it the other day with under 20 lines laughing @ myself for making life so difficult on myself back & then & still now, lol.]

[dlow]

Hi MarvinP,
In AS3 is a comment, but I would like to make the array smaller, but when I do I get #Ref Error. if I change my Index Array to AH from A thats when I get the error. I hope I explained it well enough for you to help me. I would like to say thanks to you anyway I have read a lot of your solutions. The expected out come would be like all the other cells below AS3 which still have the "A" Array. Oh one more thing AS & BH kinda work with eachother, so maybe BH would be the 1st one I should be looking @ They are almost the same formulas. Sorry Im a mess.

[dlow]

I already have a formula that works, but I want to change it, when I do it breaks it. AS3 has the broken formula, but AS4 & down has the working formulas. What I am doing to break it is changing the array AS3 from A to AH that breaks it, yeah I see that Index doesnt like my conditional colRefs & thats where its erroring out & thats what I would like to fix. I was hoping that choose would help but I cant even get that working. So I was just reiterating on the off chance that might have made it a bit clearer if not no worries thanks for your help MarvinP & FlameRetired I gave each of you some RepPoints.

[MarvinP]

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.

[dlow]

I am open to any thing that leaves the data in place so a pivot table wouldnt do it, sorting I dont see how that would help I could be to close to the problem to be seeing this intelligently, maybe some sleep will help. Always open to another formula or build-in function.

[Glenn Kennedy]

I'm confused, but does this help??

=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)))),"")

[daddylonglegs]

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))))

[samba_ravi]

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)))),"")

[dlow]

That was pretty quick; & many thx again to everyone.
The results are of course that you guys solved this & in more than one way.

Glenn Kennedy, sadly your solution didnt work 100% it did fix half the Cols, but not the other which I am sure if we thought about it enough we would figure a way to skin that cat I still gave you a RepPoint tho for taking a stab @ it for me & again I thank you. +1RepPoint

daddylonglegs it goes without saying that you solved it, you’re a master & I would personally like to thank you for all of your efforts. I have learned a lot from all that you do.[Done fawning [for now]] But seriously words cant express the gratitude & appreciation I have for you & this community. +2RepPoints

nflsales you also solved this & in a very clever way. I will have to look @ this more once I finish my 1st cup of coffee. I am very grateful for your solution too. +2RepPoints


Thanks again everybody this is Solved & I am making it so.

[dlow]

Wow!!! Thx so much folks. I am whelmed with the outpouring of information & support. I just woke-up I have to read them all & test I will get back & update ASAP. Wholly Molly; THANKS SO MUCH!!!

[samba_ravi]

thanks for your feedback and reputation

[MarvinP]

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.

[dlow]

Wooooooow...... what a reduction in overhead @ daddylonglegs absolutely brilliant, so much so it saved me from having to move the data around, too avoid having the arrays process several irrelevant Cols ect, ect... Also thanks for the very thorough explanation of what I was doing wrong