Index and Match Function

Hi

I’m trying to develop a report of Printing and Photocopying for each of our departments.

I’ve developed the attached spreadsheet.

Worksheet 1 (Data) contains the raw data from a CSV file.

Worksheet 2 (English) is the report that I want to generate. In cell B4 I’ve inserted an Index and Match formula after watching the video https://www.youtube.com/watch?v=yH_ArqoB0no. The answer in B4 (English worksheet) should be 87 (Cell G51 on the Data worksheet). The answer in B5 should be 5013 (Cell H51 on the Data worksheet).

Unfortunately, the formula returning #REF!

Could someone please help me by correcting the formulae?

Hi,

In B4 and across please try:
=INDEX(Data!G:G,MATCH(English!$A4&$A$2,Data!$F:$F&Data!$A:$A,0))

Hi Bo_Ry

Thanks for your help.

Unfortunately, when I tried to download your attached file I get an error message - see attachment.

Hi belinda200

Thanks for your help.

Unfortunately, when I tried the formula you advised for B4 I get the #VALUE! response - see attachment

In addition, when I try to download your attached file I get an error message - see attachment.

Thank you Bo_Ry and belinda

Thank you for helping me.

I managed to get the formula to work.

All the best,

AlanL