CODEWITHSUNDEEP
excel-formula
Cheng
Cheng

Reputation: 47

Index & Match 2 different data sheet for multiple column in excel

I have two different data sheet and would like to compare multiple column. If is match then return value 1.

I have tried with formula below and seems not working..Anyone know the reason or advise the correct formula instead. =INDEX('[Cancellation Raw Data 2023.xlsx]Sheet1'!$Y:$Y,MATCH(G2,'[Cancellation Raw Data 2023.xlsx]Sheet1'!$X:$X,0),MATCH(J2,'[Cancellation Raw Data 2023.xlsx]Sheet1'!$C:$C,0),MATCH(R2,'[Cancellation Raw Data 2023.xlsx]Sheet1'!$F:$F,0))

Attached below screenshot below for reference. Highlighted Yellow = Matching this 3 columns Highlighted Green = expected result if is match to show value 1 in Book1 excel sheet enter image description here enter image description here

Upvotes: 0

Views: 121

Answers (1)

Spectral Instance
Spectral Instance

Reputation: 2494

If you don't have dynamic arrays then this must be entered as an array formula (using CTRL+Shift+Enter, rather than just Enter)

=INDEX('[Cancellation Raw Data 2023.xlsx]Sheet1'!$Y2:$Y10001,MATCH(G2&J2&R2,'[Cancellation Raw Data 2023.xlsx]Sheet1'!$X2:$X10001&'[Cancellation Raw Data 2023.xlsx]Sheet1'!$C2:$C10001&'[Cancellation Raw Data 2023.xlsx]Sheet1'!$F2:$F10001,0))

(it's best to avoid full-column references, so I've assumed you have just 10k rows of data - you should adjust the references for your actual number)

Upvotes: 0

Related Questions