INDEX/MATCH with Multiple Conditions

Hi...

Please see attached. I have a client database which is used to calculate how much money is spent per client each year. We have 2000 contacts and we add at least 10 contacts every two weeks.

For our Christmas gifts, we give out dinner certificates to our clients.

Clients with a rating of C,D, or E have a set amount to their certificates... Clients with a rating A or B have open ended certificates... after their outing, the restaurant let's us know how much they spent.

I've created a way for C,D and E to have a set price (please see"event Index" tab)... but A and B are a bit trickier...

I created a separate tab in the database called "Christmas Gifts A & B"... where i was thinking to record the exact amount spent per client with a rating of "a" or "b"...

How do i get the total to appear in the Column BC under "Master Database"?

Any suggestions would be appreciated.

Hi Lost123, welcome to the forum.

A Vlookup on the last name will only work if the last name is unique. With 2000 clients it is more likely not.

You don't seem to have a unique client ID, so it might be best to combine company, first and last name as a unique identifier.

Try this Index/Match with multiple conditions in BC2 and copy down.

=IF(OR(J2="a",J2="b"),INDEX('Christmas Gifts A & B'!K:K,MATCH('Master Database'!A2&'Master Database'!B2&'Master Database'!C2,INDEX('Christmas Gifts A & B'!A:A&'Christmas Gifts A & B'!B:B&'Christmas Gifts A & B'!C:C,0),0)),"")

This should work, then. It limits the Index from row 1 to row 9999 instead of referring to the whole columns.

=IF(OR(J2="a",J2="b"),INDEX('Christmas Gifts A & B'!$K$1:$K$9999,MATCH('Master Database'!A2&'Master Database'!B2&'Master Database'!C2,INDEX('Christmas Gifts A & B'!$A$1:$A$9999&'Christmas Gifts A & B'!$B$1:$B$9999&'Christmas Gifts A & B'!$C$1:$C$9999,0),0)),"")

Note that the formula in the worksheet you attached was not identical with the formula I posted. Where my formula referred to K:K yours had a reference to J:K. Make sure to copy and paste and don't edit unless you know what you're doing!!