How to use Index Match in Accounting and Financial Analysis (Step-by-Step)

Рет қаралды 20,294

Күн бұрын

Join 10,000+ professionals who enrolled in the Controller Academy 🚀
controller-academy.com/course...
[coupon 30OFFCOURSE for 30% discount]
Or
Get my Controller bundle, which includes the Controller Academy
controller-academy.com/bundle...
[coupon 30OFFCOURSE for 30% discount]
Download your Index Match practice file:
thefincontroller.com/p/index-...
How to perform Index Match in Excel: The basics. Watch this video lecture to find out how you can use Index & Match for both basic and complex lookup problems. This video shows you how Index Match works with simple and to the point explanation. The video first shows you how Index formula works by itself and then how Match is created, and then combines the two Excel formulas together for a robust lookup each time.
Why Index Match is more superior than Vlookup & why should you use Index Match instead of Vlookup?
1. VLOOKUP searches only to the right, INDEX and MATCH can look in both directions - left and right.
2. INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix.
3. INDEX & MATCH produces less errors.
Overall, INDEX and MATCH are more flexible than VLOOKUP.
Hang Out with me on social media:
📸 / the_financial_controller
📱 / thefinancialcontroller
🙋🏼‍♂️ groups/78073...
DISCLAIMER: Links included in this description might be affiliate links. If you happen to purchase a product or service with the links that I provide I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel so I can continue to provide you with free content each week!
All views expressed on my channel are mine alone. Not intended as financial or professional advice

Пікірлер: 57

@nsavernon Жыл бұрын

I do this for all of my pre-paid schedules. I tie it into the month end date that I’ll have on the tab… my numbers just roll over without me having to do any calculations it’s brilliant

@lijuansun3778 Жыл бұрын

Great video, make it dynamic, really helpful, thanks

@TheFootballRef Жыл бұрын

clear and consise, thanks!

@axelengelmann9044 Жыл бұрын

clear and useful! thanks! regards from argentina

@lalelale9172 Жыл бұрын

Thank you so much index/match is the best 💯

@rickybrotega Жыл бұрын

Im not the most advanced in excel and didnt even know about this feature. This is going to be something really helpful in my tool bag of tricks. Thanks for sharing! Love these kinds of videos!

@TheFinancialController Жыл бұрын

Great to hear!

@donepudiramu Жыл бұрын

I like your explanation on index and match formula with your examples. Great Job ❤

@AamirMusthafa Жыл бұрын

Well explained.

@zan1971 Жыл бұрын

That's a super useful tool. Now I know how to create dynamic lookups. Xlookup I don't know if it works with match functionality so this seems better tbh.

@gsm6120 Жыл бұрын

Love it!

@TheJirator Жыл бұрын

Hello Bill, very helpful, thanks a lot!

@TheFinancialController Жыл бұрын

Glad it was helpful!

@kyungeunkim8217 Жыл бұрын

Thanks for the video

@Wilhelm96 Жыл бұрын

This is just exectly what I was looking for to learn next. Second video of yours and the explanation is great! Easy understandable for an amateur like me. Thx for sharing your knowledge! (:

@TheFinancialController Жыл бұрын

Glad it was helpful!

@vijayrajashree4694 Жыл бұрын

Thank you and it was very useful..

@TheFinancialController Жыл бұрын

Glad it was helpful!

@Totalsells Жыл бұрын

I love it, Thanks

@TheFinancialController Жыл бұрын

I'm glad you like it

@accounting_for_everyone Жыл бұрын

Hello my brother, I have been using the combination of index and match for years and it is a wonderful combination and the vlookup formula has many flaws, but in version 2021, the xlookup formula has been added and the vlookup flaws have been fixed.

@hanhtikesoe3979 Жыл бұрын

Happy New Year Bill. You have no idea how much I'm thankful to you. I'm an expat working as a financial analyst in Bangkok. If you ever had a chance to travel to Bangkok, Thailand, please let me know. I'd like to treat you something delicious.

@TheFinancialController Жыл бұрын

Happy new year! I will take you up on it, one day

@gaborsiegl Жыл бұрын

hi, that was great video, thank you! you should do more excel videos related to financial analysis I guess not I am the only financial anyalst whos weakness is excel.

@issamelsayd Жыл бұрын

thanks Sir

@eliasabdissa9406 Жыл бұрын

Nice to refereshing excel for accountant useful one thank you brother

@TheFinancialController Жыл бұрын

Always welcome

@minhluong400 Жыл бұрын

Other alternative formulas would be sumifs or sum product that will work too in your scenario given you only have one unique month on your look up array

@moaazelmansi8932 Жыл бұрын

Could you explain how to do Trial balance mapping in separated lecture?

@moose5445 Жыл бұрын

Thanks for the video, that's a handy formula! Out of curiosity, what metrics do you have on your dashboard at the very end?

@PraveenCJRegina Жыл бұрын

Thank you, Bill, for your great informative videos. Thanks to your videos, I have become a Financial Controller of a more prominent organization, especially the following videos - Five Financial Analyst Behavioral Interview Questions & Answers, Five Advanced Financial Analyst Interview Questions, and How To Analyze Financial Statements For A Corporation. Four Types of Financial Analyses & How To Read & Analyze The Balance Sheet Like a CFO. These videos helped me a lot to ace my interviews even if I have more than 17 years of experience in the Finance sector. Now I am planning to take your Controller Academy course. I am confused about whether to take the Controller bundle of 3 courses or just the Controller academy course. Any thoughts?

@TheFinancialController Жыл бұрын

Go for the bundle

@nhechao757 Жыл бұрын

Hi, is finance controller affair the same in most companies eg tech company vs manufacturing company. What I mean is that a financial controller will be easier to change companies than a accounting between different company. What do you think about this.

@nadirbaig156 Жыл бұрын

How do the depresation work with formula.

@zain4494 Жыл бұрын

please make a video for bookkeeping/accouting cycle practical at work demonstration

@imthatguy4179 Жыл бұрын

Hello Bill. I have an excel file that has 2 sheets. Sheet 1 is the balance from sheet two. Sheet two data is a tracker of raw materials for all the days for a period (let’s say January). I will like to duplicate this two sheets for the month of February. I want the ending balance in January sheet two to automatically pop up in the February sheet two as the beginning balance. So that whenever January sheet 2 update , it automatically update also in February without having to do it manually. Can I use this function or what will you recommend

@saud5503 Жыл бұрын

Hello brother I want to ask you please and i hope to get answer. Which one more flexible Accounting or finance and can accounting do finance job and finance can do accounting job? thank you so much sir

@PraveenCJRegina Жыл бұрын

Accounting is a subset of finance. Finance is the broader classification like marketing, sales, operations, human resources etc. Accounting focuses on the day-to-day flow of money in and out of an organization; finance manages money and investments. Accounting is the recording and reporting of a company’s financial records. Those who work in accounting use a standard set of rules and principles, known as the GAAP, to track and report the financial transactions of a business and often manage the general ledger, cash flow, and tax obligations. Finance professionals work in careers such as investment banking, wealth management, and financial planning and analysis (FP&A).

@christopherbaby3842 Жыл бұрын

Agent X{lookup} "No you use that old and busted formula, I drive that new hotness."

@TheFinancialController Жыл бұрын

I must check it out!

@jenniferlim2250 Жыл бұрын

Hi Bill, Formula =XLOOKUP($J$3,Table1[Month],XLOOKUP($H$5,Table1[#Headers],Table1)); I turned the data set into a table. Looks as unwieldy as the index, match one. Hvnt tested speed of this approach vs index, match.

@katestan7002 Жыл бұрын

@@TheFinancialController XLOOKUP is super great.

@nicholasshook7513 Жыл бұрын

Anyone on here recommend a double major in accounting and MIS if I want to get my CPA? Or just focus strictly on accounting?

@TheFinancialController Жыл бұрын

I would focus on accounting

@PauloSantos-kw7xc Жыл бұрын

Hey Bill, I am a controller , but I didn't start my career in a big 4 , or an accounting firm, and with that there are skills that are hard to get if a career was developed outside of those types companies. Many roles, do require you to understand Technical Accounting, which in simple terms is the application of what FASB produces to specific items in your Financial statements. I don't know if this is something you work with , but there is nothing to be found on how to develop technical accounting skills on the internet, or that even explains what this role in companies does. Also when it comes to internal controls, and the SEC there is nothing out there , outside maybe of being COSO certified that helps someone who is developing an accounting department from the ground up know what needs to be set up if you are going to go through an IPO or want to bring your company to a state of where External auditors will sign off on what you have, and have no material deficiencies , etc. So, if it's too hard to teach all of this, how does a controller go about meeting these demands even if he hasn't had specific training on them?

@TheFinancialController Жыл бұрын

Hi Paulo - many times I engage outside firms to help me write memos on technical accounting matters. I've also had situations were we hired a firm for internal control development, granted it's expensive and you need budget for it, but I am just giving you the option. A cheap way is attend controller/accounting groups, pick people brains and draft your own memo/controls and the have outside firm review (cheaper than actually coming up with). Hope this helps. great questions