im using INDEX and MATCH with excel to return a value i want but can i make it so in the match it has to match two sets of data instead one set?

Question

I get the idea of how to use Index and Match ;-

=INDEX(Sheet1!C:C,MATCH(Sheet1!B:B,Sheet2!B:B,0))

The issue I'm facing is that, Sheet 1 and Sheet 2 have data pulled from different sources (but has the same headings).

I want to try to use the above Index and Match to bring back data that is compared to two different sets of data.

Both Sheet will have the following heading lets say ;-

The data I want pulled back is data from "Size", I can use the above formula for "Firstname" and "Surname", but data will be different, so i want to use this to find the differences, I don't mind the values returning as N/A as that will work for me in this task, but can I do it in one formula ?

Sheet1 :-

Sheet2 :-

From the above image, you can see the dataset is mixed up and if it was real data i would use in the workplace, the data in Fristname could be different format, such "MPS1_"

Thanks

Would be helpful to include some example data and expected results here. It's not clear what you mean by "i want to use this to find the differences" — Tim Williams, Mar 06 '19 at 22:41
Will the list of names be unique, no duplicates per sheet list? — Scott Craner, Mar 06 '19 at 22:47
Yes, the names will be Unique and will contain no duplicates, but duplicates will be in the 2nd col — halo_4, Mar 06 '19 at 22:49

score 0 · Accepted Answer · answered Mar 06 '19 at 22:49

0

In C2 on Sheet2 put:

=SUMIFS(Sheet1!C:C,Sheet1!A:A,A2,Sheet1!B:B,B2)

And copy down

answered Mar 06 '19 at 22:49

Scott Craner

148,073
10
49
81

Think this did the trick. can you explain how this formula works ? – halo_4 Mar 06 '19 at 22:54
1

Since the return is a number and the combination of first name and last name is unique it will only find one number to sum and thus return the number desired or `0` if no match is found. – Scott Craner Mar 06 '19 at 22:56
ok, what if the return value is text ? – halo_4 Mar 06 '19 at 23:01
see here: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Mar 06 '19 at 23:04

im using INDEX and MATCH with excel to return a value i want but can i make it so in the match it has to match two sets of data instead one set?

1 Answers1