Multiple Match criteria based on two worksheets , which contain duplicate id numbers , so need to check additional data in matched row

Asked Jan 31 '20 at 15:26

Active Feb 05 '20 at 16:34

Viewed 45 times

I have two worksheets that need comparing, sheet1 has an ID code in column A , in sheet2 there are multiple duplicates of this ID number. So I need a formula to also check if the matched row data in sheet2 , also has a matched value in matched row of Sheet2.column D , with the row the formula is checking in sheet1 column G and then if matched row of Sheet2.column I , with the row the formula is checking in sheet1 column Z . If all values are added then I need to Add the value of Sheet2.Column B to where the formula is.

The formula I tried to write would be something like this , but I just can work out how to do multiple match checks

=INDEX('Sheet1'!$B:$B,MATCH(@'Sheet1'!$A:$A,'Sheet2'!$A:$A,0),1) and MATCH(@'Sheet1'!$G:$G,'Sheet2'!$D:$D,0),1) and MATCH(@'Sheet1'!$Z:$Z,'Sheet2'!$I:$I,0),1)

edited Feb 05 '20 at 16:34

braX

11,506
5
20
33

asked Jan 31 '20 at 15:26

John

See: https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another – Scott Craner Jan 31 '20 at 15:32
thanks I had a look at the link , but nothing in there made sense to me , regarding my issue – John Jan 31 '20 at 15:37
how did you try using any of those formula in your situation? – Scott Craner Jan 31 '20 at 15:40
What version of Excel are you using? – Scott Craner Jan 31 '20 at 15:41
the version is office 365 – John Jan 31 '20 at 15:46
i have about 35,000 rows of data in sheet1 and 250,000 rows of data in sheet2 , sheet2 has many duplicated ID numbers , which is why I needed the formula to only bring back value if all criteria is matched in additional columns. So the exact match is found. Hope this makes sense – John Jan 31 '20 at 15:48
Use the FILTER approach then. – Scott Craner Jan 31 '20 at 16:00
Thanks could you kindly provide me with an example of how it should look – John Jan 31 '20 at 16:14
I did in the link above. – Scott Craner Jan 31 '20 at 16:15
well I tried the filter formula but it did not wor can you please let me know what I have done wrong = FILTER('Sheet2'!A:Z,('Sheet2'!A:A='Sheet1'!A1)*('Sheet2'!D:D='Sheet1'!G1)*('Sheet2'!I:I='Sheet1'!Z1),'Sheet1'!B1) – John Jan 31 '20 at 17:02
`FILTER('Sheet2'!B:B,('Sheet2'!A:A='Sheet1'!A1)*('Sheet2'!D:D='Sheet1'!G1)*('Sheet2'!I:I='Sheet1'!Z1))` – Scott Craner Jan 31 '20 at 18:03
I still get #CALC! errors – John Feb 04 '20 at 08:30
I finally managed to get it to work , but due to there being 26,000 rows to be checked and 68,000 rows of data in sheet2 . the formula takes 35 minutes to update , is there any alternative solutions in VBA – John Feb 05 '20 at 15:58
yes, look into advanced filter. – Scott Craner Feb 05 '20 at 15:59
okay where is that please , is that VBA or formula – John Feb 05 '20 at 16:00
It is a built in function. It is under the data tab. but you can use vba to automate it. There are many sites out there that have example code and discuss how to use it. – Scott Craner Feb 05 '20 at 16:01

Multiple Match criteria based on two worksheets , which contain duplicate id numbers , so need to check additional data in matched row

0 Answers0