How to find matching values between 2 excel sheets.

1
votes

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

1
excelexcel-formula
I have not fully understood your problem. Maybe it would help if you show more of your data. But I have the feeling that the MATCH function and or VLOOKUP may help you. – Paul B.
vlookup works except it only gives me the first value it finds – user1442105
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. – user1442105
You can use MATCH to find all lines in the second sheet for which in column A there is a corresponding value on the first. BTW, you can edit your question to included additional information. – Paul B.
I suggest using an array formula. The "recommended blog articles" also provide several similar examples. This will let you get all matches in a range from a MATCH or VLOOKUP. – Zairja

1 Answers

1
votes

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.