How to link two files in Excel - 2 ways to solve the problem

I have a price list Excel workbook & sales data workbook. In this video, let's combine these two files using Excel.

In the video...
===========
0:00 - File connection problem
0:53 - Using formulas to link two files
1:38 - The problem with formula approach
2:14 - Another way to connect two files (Power Query)
3:49 - What happens when you have new data?

Sample files:
===========

🤔Curious about Power Query? Learn more from these videos
===================================================

Want to learn a LOT of Excel in ONE PLACE:
===================================

~ Have a beautiful day 🌼🌞😀

[proton]

I've been struggling with this, just in time, my man comes swooping in with the right solution. Thank you!!!

[jojieyabo]

educational and entertaining. The humor is impeccable :)

[mariyatsvetanova]

Thank you, Chandoo! I knew the first function but it really break when you close the file. And it never occured to me to use the second one. We use only tk convert reports and they to be more readble. Thank you, again!

[graceamah]

Thank you so much @Chandoo, this tutorial came in on time, i even feel like this video is for me. thanks for yet another amazing video

[BillSzysz]

Thanks Chandoo,
As you probably know, I like PQ a lot but in such a simple case, the INDEX-MATCH combination will do very well (works on closed files)

[zaidqureshi]

Thanks you very much Chandoo for sample files.

[maskedhearts]

Thanks for the info..👍

💚💚💚I use index & match, it works even if the indexing file is in closed status.💚💚💚

[wayneedmondson]

Nice tip Chandoo! Always creative and useful tricks at your channel. Thanks for demonstrating. Thumbs up!!

[superboyjamie]

You can also import all the data into an Access database and create a relationship between the tables.

[vijayarjunwadkar]

Nice video as usual, the Power Query way is best! Thanks for sharing! 😊👍

[surajbhardwaj]

Connecting🖇️ two or more file📗 with this Power query technique is really awesome👌.Thank you so much Sir....😀😀

[mohammadfaizanamir]

Thank chandooo ... Your videos help me a lot!!!

[surajbhardwaj]

You are the best..always .... Chandoo👌💚💚

[Will.Tru]

man you kill me with your jokes! God bless!🤣

[atultripathi]

its better to use sql...excel is not a db

[kamilradziemski]

Hi Chandoo, good video as always ; )
It would nice to mention that the #REF error appears only if we reference to a excel table. There is no issue if we use reference to the regular range.
Cheers! : )

[Bhavik_Khatri]

Nice little example. Thank you very much.

[atifakbar]

Awesome technique just like awesome chocolate 😊👍. Thank you

[trischurch]

Thanks, but what if the spreadsheets are stored within a Teams or Sharepoint site?

[muhammadtambawala]

Thanks Chandoo for your awesome videos.

SUMPRODUCT formula works well for me in this scenario.