IndexColumn Help

[dlow]

Hi,

I know what row the data that I want is in, but it could be in 1 of 2 non-contiguous columns. I was thinking I could use IndexMatch, but when I tried to do it & make the column conditional with an ifStatement I get a #Ref so I must be doing it wrong. How would you guys do it.

Heres my lame formula:

Please Login or Register  to view this content.

What I am trying to do is look in the known Row & if Col CK has a value use CK as the Col Ref if not Col CM has it.


As always TIA

Update:

I am still trying to do all of the above, but my problem isn’t the condition that I’m giving Excel my Col, because if I hardCoded it with an integer like 4 I still get #Ref, so I just don’t know how to do Index using a Col Ref. I will check with Google thx guys

[protonLeah]

Please Login or Register  to view this content.

[dlow]

Hi thx for the quick reply, sadly I am still getting #Ref[even with CAE[CntlAltEnter]], so I am going to make a sample workbook to make it easier for you guys to help me out. I am hoping that in just doing that I might realize the error of my ways.

Thx again for the help protonLeah

[dlow]

Heres a workBook thx again.

FindMe.xlsb

[martindwilson]

you need the whole range for the index since your formula is looking for row and column
=INDEX(A3:F201,MATCH(A3,A3:A201,0),IF(D3<>"",COLUMN(D3),COLUMN(F3)))
red bit is row green bit column

[dlow]

Like magic thx martindwilson & protonLeah +1 Rep for you both. This is Solved

Please Login or Register  to view this content.

[protonLeah]

Sorry I did not notice the problems with your formula.
In the Index, you have a single cell, BP3, where an array is required.

Unfortunately, the workbook you posted does not correspond to the original posted formula. Also, in the workbook the formula in H3 is trying to match a date in A3 "12/05/2014" with dates below, but the latest date is "12/04/2014", so there can be no match. If there were, the formula for the posted worksheet might look like (if the index array includes column A):

Please Login or Register  to view this content.

or maybe this, if the data is only returned from columns D or F:

Please Login or Register  to view this content.

But, you will have to post a more accurate sample workbook.

[dlow]

Nothing to apologize about I appreciate your time & I used your condition in my final formula.
Sorry my sample workbook wasn’t on point. Yeah I was just about to update my post & change the title realizing that the problem was that I didn’t know how to do index with a Col Ref.
Thx for the additional insights.

Update:
lol @ myself; yeah, what you guys said: the "Array" I got my brain wrapped around it now. Thx again.
Fin Formula:

Please Login or Register  to view this content.