Using Index & Match always results in #N/A

[ioswoody]

This is my formula: =INDEX(Paste!$B$21:$M$26,MATCH(A12,Paste!$A$21:$A$26,0),MATCH(C8,Paste!$B$20:$M$20,0))

Trying to bring the 'Sales (Qty)' (Row) from the Month (Column), which is on another sheet.

This is the data that is on the other sheet:
Consumer New April May Jun Jul Aug Sept Oct Nov Dec Jan Feb Mar
Rank 34 12 6 - - - - - - - - -
Score 67 90 90 90 - - - - - - - -
Target 86 78 81 - - - - - - - - -
Sales (Qty) 64 111 81 4 - - - - - - - -
Total Comms 9681 17302 12700 725 - - - - - - - -
Avg Comms 151.27 155.87 156.79 181.25 - - - - - - - -

What am I doing wrong?

Thanks in advance

[protonLeah]

Use HLOOKUP instead:

Please Login or Register  to view this content.

where C8 holds the month

[ioswoody]

Thanks for this. I had tried HLOOKUP as well without success

When is used this one I was still getting #N/A, then it dawned on me that I was pasting the data as TEXT! Re-pasted and all working well - Thanks!

Some of the data has '-' in it, how can I get these to show as '0'?

Thanks again

[protonLeah]

If the values are alway going to be numbers or "-" you can try:

Please Login or Register  to view this content.

I don't know if "IFERROR()" is available for the Mac, though.