Wildcard MATCH

[johnmitch38]

On the attached, I have a a table of percentages per customer, A1:B5.

My data is in A10:B17. My current formula in column C finds the relevant percentage per customer and adds it to the values in column B.

This works using INDEX and MATCH to return the necessry % for each customer.

However, cells C11 and C14 are errors because they are not exact matches. Can I adapt my formula to include a wildcard INDEX/MATCH?

Current formula is:-

=SUM(B10+SUM(B10*INDEX($A$2:$B$5,MATCH(A10,$A$2:$A$5,0),2)))

[Special-K]

Cant answer your problem but here's something extra

Your formula:
=SUM(B10+SUM(B10*INDEX($A$2:$B$5,MATCH(A10,$A$2:$A$5,0),2)))

You dont need to SUM(A+B), its simply A+B
So

=B10+SUM(B10*INDEX($A$2:$B$5,MATCH(A10,$A$2:$A$5,0),2))

[boopathiraja]

Hi, Apply the below formula in C10 and drag it down.

Please Login or Register  to view this content.

[Pete_UK]

You can change your formula in C10 to this:

=B10*(1+INDEX($A$2:$B$5,MATCH(SUBSTITUTE(TRIM(A10)," (new customer)",""),$A$2:$A$5,0),2))

then copy down.

Hope this helps.

Pete

[johnmitch38]

Hi, Apply the below formula in C10 and drag it down.

Please Login or Register  to view this content.

This is perfect - thank you.