Excel 2007 : Matching Multiple Criteria

[savio21]

Hi,

I am trying to figure out a formula in excel that will look up multiple criteria on multiple columns to return a single value. I have provided an example below.

Sheet 1:

Column A / Columnb B / Column C
Abe / A1,A2,A3 / 10
Abe / A4,A5,A6 / 20
Bob / A2,A4,A6 / 30

Sheet 2

Column A / Column B / Column C
Abe / A1 / 2
Abe / A3 / 3
Abe / A4 / 4
Bob / A4 / 10

Sheet 3

Column A / Columnb B / Column C
Abe / A1,A2,A3 / 5 (10-2-3)
Abe / A4,A5,A6 / 16 (20-4)
Bob / A2,A4,A6 / 20 (30-10)

The result im looking for is to look up the values in sheet 2 match in sheet 1 under column A. Then do a second match from column B in sheet 2 to sheet 1 and then subtract the values in running total and output to a new sheet. I hope thats clear

Thanks in advance for your help

[DonkeyOte]

I'm not sure I'm following entirely but..

Please Login or Register  to view this content.

where Sheet1 & Sheet2 are as you depicted in your original post

[savio21]

Thanks DO, it seems to work for the basic example I provided but its having issues when I'm trying to replicate it for my table.

It seems to be missing a lot of multiple entires and only returning the one with highest values. Also is it possible to change the exact match criteria on first column. Ill provide an example below. I think resolving the second issue should resolve the first

Thanks again,


Ex 1:

Using the same examples as previously I need to change the match criteria a little bit. I bolded the new part I added in.

Sheet 1

Column A / Columnb B / Column C
Abe B. / A1,A2,A3 / 10
Abe C./ A4,A5,A6 / 20
Bob D. / A2,A4,A6 / 30

Sheet 2
remains the same

I need sheet 3 to do the same as before except now onceit matches the Abes it needs to know which one to subtract from. What I noticed in the code you provided its able to match easily where the text matches exactly. But for some with additional names it fails the first test and provides no update.

[DonkeyOte]

It would help if you outlined the expected results for the revised samples.

If I assume they are meant to be unchanged then:

Please Login or Register  to view this content.

but you're open to errors on the partial name match (eg Gabe) - if the names on Sheet1 follow a certain rule (eg Name Space Initial etc) then you can limit the possibility of a false positive

[savio21]

Sorry the results should be exactly the same as above.

Unfortunately, the problem I have is that some of the values have random spaces in between text or trailing the text. I think this might be causing some of the trouble in matching.

[DonkeyOte]

The prior suggestion worked based on the revisions you stipulated.

If as you are implying some of the criteria values contain spaces and you have superfluous spaces in the search strings you can try applying a TRIM to the search values

Please Login or Register  to view this content.

Failing that I'm afraid you will have to post a sample file that reflects the issues you are facing else I am in effect guessing as to your real issue(s).

[savio21]

I have attached the section that I'm having issues with. Everything is copied and pasted with the same formatting with the table I'm working with.

Hopefully that makes more sense, and I really appreciate all your help DO.

[DonkeyOte]

The issue is a line feed character.

Please Login or Register  to view this content.

[savio21]

Awesome that worked great, i just had to run a trim on sheet 2 to clear out the extra spaces.

Thank you so much DonkeyOte