Nested Index/Match/Match problems

Apologies, please find attached dummy file. Thanks Pete.

I am trying to write a formula where if there is no information in the first column, then look to the next column to see if there is anything there, if something is found then execute the formula. I have managed to get it working for two columns, however not sure if that is the limitations with the formula I'm using.

This will remedy that part. Embed it in your formula where needed and you will not have to check each column separately. Try 'demoing' it in Z3 and fill down.

Formula:

=MATCH(1,INDEX(--ISNUMBER($L3:$W3),0),0)

Then try in AA3

Formula:

=INDEX($L3:$W3,MATCH(1,INDEX(--ISNUMBER($L3:$W3),0),0))

That will return the first number. Then this in AB3

Formula:

=INDEX($L$2:$W$2,MATCH(1,INDEX(--ISNUMBER($L3:$W3),0),0))

That will return the 'L#.#' headers.

I haven't examined the rest of your formula thoroughly. I couldn't help noticing the relative cell/range references. It appears you are hand typing each formula in each row. You can save yourself a lot of grief and work if you get acquainted with relative/absolute cell referencing. (That's what the $ signs are about.)

With that thought in mind I encourage you to read up here: Relative and Absolute Cell References

Does any of this help?

Good deal!

"doesn't work" is not helpful information.

Please be more specific.

Try this in Y3 and fill down.

Formula:

=IFERROR(IF(OR(I3="y",J3="y"),INDEX(Sheet2!$C$42:$N$56,MATCH(INDEX($L3:$W3,MATCH(1,INDEX(--ISNUMBER($L3:$W3),0),0)),Sheet2!$B$42:$B$56,0),MATCH(INDEX($L$2:$W$2,MATCH(1,INDEX(--ISNUMBER($L3:$W3),0),0)),Sheet2!$C$41:$N$41,0))*E3*X3,IF(AND(I3="n",J3="n"),INDEX(Sheet2!$C$23:$N$37,MATCH(INDEX($L3:$W3,MATCH(1,INDEX(--ISNUMBER($L3:$W3),0),0)),Sheet2!$B$23:$B$37,0),MATCH(INDEX($L$2:$W$2,MATCH(1,INDEX(--ISNUMBER($L3:$W3),0),0)),Sheet2!$C$22:$N$22,0))*E3*X3)),0)

It returns this at my end.

	Y
2	Wages
3	1,309.50
4	1,047.60
5	1,047.60
6	1,204.74
7	-
8	-
9	-
10	-
11	1,673.25
12	1,338.60
13	-
14	-
15	-
16	-
17	-
18	-
19	-
20	-
21	-
22	-
23	-
24	-
25	-

1 - Using MAX to get the number in row 11 (Assuming there is only one figure in L:V):
MAX($L11:$V11)
2 - Using LOOKUP to get row title:
LOOKUP(MAX($L11:$V11),$L11:$V11,$L$2:$V$2)
3 - We use the range B22:M37 only, penalty range will be B22:B37*1.15
Using if statement:
IF(I11&J11="NN",1,1.15)
4- Using VLOOKUP (or INDEX(MATCH...)
VLOOKUP(MAX($L11:$V11),Sheet2!$B$22:$M$37,MATCH(LOOKUP(MAX($L11:$V11),$L11:$V11,$L$2:$V$2),Sheet2!$B$22:$M$22,0),0)*E11*X11*(IF(I11&J11="NN",1,1.15))
5- Error trap:

=IFERROR(VLOOKUP(MAX($L11:$V11),Sheet2!$B$22:$M$37,MATCH(LOOKUP(MAX($L11:$V11),$L11:$V11,$L$2:$V$2),Sheet2!$B$22:$M$22,0),0)*E11*X11*(IF(I11&J11="NN",1,1.15)),0)

You are welcome. Glad to help.