Compare Sheet1 to Master sheet and return Master sheet data

[drewber]

I have Sheet 1 with data and the Master sheet. Due to a sorting error some of the data on Sheet 1 is not correct. The constant is the Episode ID. I have been using Index-Match to locate the Episode ID on both Sheet 1 and Master. However there are 40+ columns on both sheets.

What I need: take Episode ID from Sheet1, locate the ID on Master and return that entire row's data from Master into Final sheet.

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your 2 sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Alex Blakenburg]

Can't you just use Filter or XLookup eg

20240716 XLookup get entire row drewber.xlsx
	A	B	C	D	E	F	G	H
1	Episode_ID	Current PCP	Department Name	Region		Current PCP	Department Name	Region
2	195922633	Andrews	City West	South		Andrews	City West	South
3	2058254
4	2258974536
5	2865876324	Gogh	City North	East		Gogh	City North	East
6
7
8		Filter				Xlookup
Sheet1

Cell Formulas
Range	Formula
B2:D2,B5:D5,B3:B4	B2	=FILTER(Master!$B$2:$D$7,Master!$A$2:$A$7=$A2,"")
F2:H2,F5:H5,F3:F4	F2	=XLOOKUP($A2,Master!$A$2:$A$7,Master!$B$2:$D$7,"")
Dynamic array formulas.

 

[drewber]

Thank you XLookup with some tweaks worked for me. Knew it was simple, but simple is not my name.

 

[Alex Blakenburg]

No problem. Thanks for letting us know. We all fall for making it more complicated than in needs to be from time to time.