INDEX - correct row/col - returns incorrect value?

[Camel123]

Hi,

Please see formula:
=IFERROR(INDEX('hours'!F5:BF2000;MATCH(P3;'hours'!A:A;0);MATCH(Q2;'hours'!2:2;0));"")

It identifies row 6 & col 6= correct. But it does not return that value. What might be the issue?

 

[steve the fish]

The fact that your index array starts at row 5 and column 6 whilst your match formulas start at row 1 and column 1. They must be the same for that to work properly.

 

[steve the fish]

Like this:

=IFERROR(INDEX('hours'!F5:BF2000;MATCH(P3;'hours'!A5:A2000;0);MATCH(Q2;'hours'!F2:BF2;0));"")

 

[Camel123]

Thank you,

Are there any work arounds on this issue? Can't change where the information appears due to the sheets being standard templates.

 

[steve the fish]

It isnt an issue. You have an index array and you provide it, in your case, the row number and the column number. Do not confuse those with the actual row and column number. They are the row and column of the index array. So =INDEX('hours'!F5:BF2000,1,1) will produce whatever is in F5 not whatever is in A1.

 

[Camel123]

Thank you Steve, issue solved! Got it working. Appreciate it.