nested IF(OR(OR vs V/H LOOKUP vs INDEX/MATCH - can't get them to work

[AlixNB]

Hi all, please can anyone help with the following:

In English I want.... a formula in Column K to search an array of columns (in only the same row)eg. Row20 and look for a value of "1"
Once that value is found... I need K20(home cell) to display the info at the top of the column the "1" was found in, which sits on R18.... accross the top of the array.

Within the array, each cell is doing a check, so the results showing in the array could be FALSE, 0, 1 (false if nothing to process, 0 if incorrect, and 1 if correct.
The array i need K20 to search is AV20:CF20 [K21 would search AV21:CF21 ...etc] the formula in each cell for the Array changes slightly, as the month changes... and as this is a two-year spreadsheet I've banded them into 6 to make it easier. so the first six cells (AV21:BA21) read:
=IF(E20>(AS$20-1), IF(E20<(AT$20+1), 1, 0))
=IF(E20>(AS$20-1), IF(E20<(AT$20+1), 1, 0))
...
=IF(E20>(AS$25-1), IF(E20<(AT$25+1), 1, 0))

E20 is a date, idividual to the record row... and non-sequential... a sign-up date.
AS20:AT20 is the start and end date of a 4 week period: our sign-up block. to picture it - the 26/01/2013 to 22/02/2013 is the Feb sign-up block. So any date in E20, that falls between those dates would show a "1" in Feb-13 column check (example above).

The problem i have... is i cannot nest more than 7 IF( in K20 to search through the columns for the "1" and tell me what block the date is in.

I've tried:
=IF(OR(OR(AV22=1,$AV$18,IF(AW22=1,$AW$18,IF(AX22=1,$AX$18,IF(AY22=1,$AY$18,IF(AZ22=1,$AZ$18,IF(BA22=1,$BA$18,CH22)))))),IF(BB22=1,$BB$18,
IF(BC22=1,$BC$18,IF(BD22=1,$BD$18,IF(BE22=1,$BE$18,IF(BF22=1,$BF$18,IF(BG22=1,$BG$18,CH22))))))),IF(BH22=1,$BH$18,IF($BI$20=1,$BI$18,IF($BJ$21=1,$BJ$18,
IF($BK$21=1,$BK$18,IF($BL$21=1,$BL$18,CH21))))))

But the block it displays doesn't change as the sign-up date does.

I've tried:
=HLOOKUP("1",CHOOSE(3,"FALSE",0,1),$AV20:$CF20,$AV$18:$CF$18)

but i get #VALUE!

I've tried:
=IF(OR(OR(AV21=1,$AV$18,IF(AW21=1,$AW$18,IF(AX21=1,$AX$18,IF(AY21=1,$AY$18,IF(AZ21=1,$AZ$18,IF(BA21=1,$BA$18,CH21)))))),IF(BB21=1,$BB$18,IF(BC21=1,$BC$18,
IF(BD21=1,$BD$18,IF(BE21=1,$BE$18,IF(BF21=1,$BF$18,IF(BG21=1,$BG$18,CH21))))))),IF(BH21=1,$BH$18,IF($BI$20=1,$BI$18,IF($BJ$21=1,$BJ$18,IF($BK$21=1,$BK$18,
IF($BL$21=1,$BL$18,IF(BM21=1,$BM$18,CH21))))))),THEN(IF(BN21=1,$BN$18,IF(BO21=1,BO18,IF(BP21=1,BP18,IF(BQ21=1,BQ18,IF(BR21=1,BR18,IF(BS21=1,BS18,CH21)))))))

But I get #NAME?

I can't seem to nest another OR with the two I already have (unless i replace the THEN with *OR --- which of course is then multipling the two halves)
ELSE isn't working
and I've looked into VLOOKUP and HLOOKUP and MATCH and INDEX but I can't get my head around it.

Normally I'd do in VB... but as each row is self contained... i'm not sure how to do it without writing 1107 macros (for each row)

Please help ASAP

 

[Jonmo1]

Welcome to the board.

Try

=INDEX($AV$18:$CF$18,MATCH(1,$AV20:$CF20,0))

 

[AlixNB]

Hi Jonmo1

Thanks for the quick response.... but:

I've just tried it and it's throwing back #N/A (the custom format of K20 is mmm-yy, would that have anything to do with it?)

when i evaluate the error, it's showing =INDEX($AV18:$CF18,MATCH(1,$AV23:$CF23,0))
so the Match onwards is the part that it doesn't like.

how would i fix that?

thanks

 

[AlixNB]

Jonmo1 !!!!!

Thank you!!! it works... i just hadn't turned the update formulas on again!

I think I must have been getting the arrays the wrong way round whenever i'd tried it as i'd always put the AV20 before the $18

You have just saved me my third night of trying to fight with nested IFs

thanks again

 

[Jonmo1]

Glad to help, thanks for the feedback.