Index and Match 3 entries in a Database

Hi

I now have completed a database (thanks to NBVC in other post) which I need to be used to pull information from.

On the "userform" sheet the user will only select two variables, Customer and Month.

In the cells F15:F174 I require an index and match formula which matches the following on the “usersheet”:

• The Customer Name (Cell C3)
• The Month (Cell C5)
• And the products (Cells B15:E174)

With the corresponding cells on the M2 sheet where the database is held. These can be found in the following locations:

• The Customer Name (Column D)
• The Month (Column E)
• The products (Row 3 which uses concatenate already)

Can anyone please help with a formula?

Hi,

A couple of Qs first.
Why do the concatenate cells on sheet M2 row F use rows 4,5,6 & 8, and not 4,5,6, & 7 which are the values in columns B:E of the user sheet?
You would simplify the MATCH() function that will be required if you had a helper column on the M2 sheet which concatenated the Customer Name and Month. Is that allowable?

There is no column in UserSheet for row 8 headers in M2 sheet, which is part of the concatenated cells in row 3 of M2...

Once you correct the formula in all the row headers in row 3 of M2 sheet, then try:

=SUMPRODUCT(('M2'!$D$9:$D$44=$C$3)*('M2'!$E$9:$E$44=LEFT($C$5,3))*('M2'!$F$3:$FI$3=$B15&$C15&$D15&$E15&$F15),'M2'!$F$9:$FI$44)

copied down..

No need for the helper in column A.

Also, the #N/A in D10:F10 is due to the April in your list at W1 having a trailing space... correct that and reselect April from your drop down to get results.

Have you taken the updated Concatenate formula and copied across row 3 of the M2 sheet?