two value index not working

[chriswrcg]

I am trying to use a two value index to return a percentage number, I have included my spreadsheet for assistance.

The formula is in cell M3. I cant figure out why it is not working.

[Fluff13]

It needs to be confirmed with Ctrl Shift Enter, not just Enter.

[BanginMyHeadOnMyDesk]

It's an array formula and therefore needs to be confirmed with Ctrl+Shift+Enter rather than just Enter.

You can tell if you've done it correctly as the formula will be wrapped in { }

Beth.

[Pete_UK]

The formula in M3 is an array formula, and this means that you need to commit it using the key combination of Ctrl-Shift and Enter (CSE), rather than the usual Enter. If you do this correctly, then Excel will insert curly brackets { and } around the formula when viewed in the formula bar, but you must not type these yourself. If you need to amend or edit the formula in some way, then you need to use CSE again.

You can copy the formula down using your usual method(s).

Hope this helps.

Pete

[chriswrcg]

two questions. can I pull this formula down to copy to the other cells and if not is there a way to do this that is is not an array formula?

[Fluff13]

You will need to change it to
=INDEX($H$3:$H$200, MATCH(1,($L$1=$B$3:$B$200)*($M$2=$C$3:$C$200),0))
Confirmed with Ctrl Shift Enter, then you can drag it down as normal.

[BanginMyHeadOnMyDesk]

Once you've confirmed the formula correctly, yes you can copy it down by dragging it.

Beth.

[chriswrcg]

I did execute the array and it worked with a problem. I need the formula to return by the line. When I executed the formula it returned the nearest correct answer that it could find. I fixed this by tightening up the array so that is only looks at the current row but when i copied down it did not work again.

This is the formula right now.

Please Login or Register  to view this content.

[Fluff13]

It's not entirely clear what you are trying to do, but does this work
=IF(AND(B3=$L$1,C3=$M$2),H3,"")

[chriswrcg]

Yes that's it exactly!!

[Fluff13]

You're welcome & thanks for the feedback