CODEWITHSUNDEEP
excelexcel-formula
user1442105
user1442105

Reputation: 11

How to find matching values between 2 excel sheets.

I have 2 columns A and B, in sheet X and 2 columns A and B in sheet Y, column A has a few different values but some are the same eg row 1=42 row 2=42 row 3=43. I want to know if the value in Column B match's on both sheets against column A. is there a formula I can use to find this?

in column A each row is a number eg 42, 42, 42, 43, 43 then in column B there is a different number for each row eg 42-333, 42-345, 42-678, 43-999. so when I vlookup i would get the 1st number it finds next to 42 for every row that is 42 but I need to find all the different values that are next to 42

Upvotes: 1

Views: 21476

Answers (1)

pnuts
pnuts

Reputation: 59440

Please try:

=IF(COLUMN()>COUNTIF(Y!$A:$A,$A1)+2,"",INDEX(Y!$B:$B,MATCH($A1,Y!$A$1:$A$10,0)+COLUMN()-3))

in C1 of sheet X copied across (say to ColumnI) and down to suit. Change the Ys to Xs to use in C1 of sheet Y.

To help identify matches, put in J1 of each sheet =IFERROR(MATCH(B1,C1:I1,0),"") and copy down to suit.

Upvotes: 1

Related Questions