IndexColumn Help

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:

Formula:

=INDEX(BP3,MATCH(BU3,A1:A201,0),IF(CK3<"",COLUMN(CK3),IF(CM3<"",COLUMN(CM3))))

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

=index(bp3,match(bu3,a1:a201,0),if(ck3<>"",column(ck3),column(cm3)))

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

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

=INDEX(A4:F201,MATCH($A$3,A4:A201,0),IF(D3<>"",4,6))

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

=INDEX(D4:F201,MATCH($A$3,A4:A201,0),IF(D3<>"",1,3))

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

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:

Formula:

=IF(BY3<>"",CELL("address",INDEX($A$3:CY201,MATCH(BY3,$A$3:A201,0),IF(CW3<>"",COLUMN(CW3),COLUMN(CY3)))),"")