Index formula not working or advise best formula

[Excel-Access]

Can someone please assit me as I have been trying to work out why the formul is not picking the correct number for the last hour with little luck.

In the attached spreadsheet M48, N48 & M49, N49. The formula is not working after multiplying by the column J.

When column J is positive you would expect columns M and N to give a postive number.

When column J is negative it should be negative in M and N.

I would be grateful for any guidance or better way of writing the formula.

Thanks a million

[NBVC]

I'm not sure what the problem is.. if I type a positive number in J28, I get positive results, if I type a negative number I get negative results... where exactly is the issue?

[Excel-Access]

Thank you for your response. The amount that I expect is not being calculated correctly as shown in COLUMN R

[NBVC]

In Row 22 of the Tracker sheet, at column where EU is shown in row 3, the amount is 73,906 if you multiply by J28 (which is currently 1), you get 73,906. Why/when should M28 be 46,077?

[Ace_XL]

You are looking up row 22 and expecting answers of Row 5. Perhaps this is what you need

Replace Data!$B$22:$L$22 with Data!$B$5:$L$5

[Excel-Access]

Column m should pick up row 5 from the data sheet and column n should be the sum of row 6 to row 20 depending on the grade for example eu and then it considers whether column j is positive or negative.

Hope this helps.

in row 22 of the tracker sheet, at column where eu is shown in row 3, the amount is 73,906 if you multiply by j28 (which is currently 1), you get 73,906. Why/when should m28 be 46,077?

[NBVC]

Then it is as Ace_XL says.. you should be looking at row 5 in the formul in M28.

=IF(ISNA(MATCH(L48,Data!$B$3:$L$3,0)),"",(INDEX(Data!$B$5:$L$5,1,MATCH(L48,Data!$B$3:$L$3,0)))*J48)

[Ace_XL]

Use

In M48
=IF(ISNA(MATCH(L48,Data!$B$3:$L$3,0)),"",(INDEX(Data!$B$5:$L$5,1,MATCH(L48,Data!$B$3:$L$3,0)))*J48)
In N48
=IF(ISNA(MATCH(L48,Data!$B$3:$L$3,0)),"",(INDEX(Data!$B$22:$L$22,1,MATCH(L48,Data!$B$3:$L$3,0))-ABS(M48))*J48)

[Excel-Access]

Thanks a million - you have solved the problem