Report Table: VLookUp, Match, Index Formula

Hi,


Please see attached spreadsheet for confirmation.

I want to produce a table which will form the basis of a report.
There are 3 Schemes (A,B,C)

As the data is inputted in column A and B I would like this to be reported in the Scheme Report Table.

Therefore for each SETTING I will get a COUNT of each Scheme for each SETTING.

I have filled in the report table with the information that the formula would show.


I believe this is done by a combination of formulas INDEX, MATCH and VLOOKUP.


Thanks for your help….

In F4, try:

=SUMPRODUCT(--($A$2:$A$8=$E4),--($B$2:$B$8=F$3))

copied down and over...

adjust ranges to suit.

O means no exact matching combination? Is that the case? If not, check that your inputs don't have extra spacing or incorrect spelling...

To hide the 0's, custom format the table as 0;-0;;@

In fact, as I look back at your table, some of the items in column E, like Acorn and Appletree have trailing spaces.. get rid of them... and presto!

Go to E4 and hit F2.. you will see there is an extra space after Acorn... check the rest too.

See attached.

Which one is incorrect?

Yes, it can... just preceed the ranges with the sheetname they are in enclosing the name is single apostrophe's and adding exclamation point before referenced range...

e.g.

=SUMPRODUCT(--('Reference Sheet'!$A$2:$A$8=$E4),--('Reference Sheet'!$B$2:$B$8=F$3))

See attached, Sheet 2 refers to Sheet 1 for data...