#Microsoft excel formulas pdf how to

Its name may fool you into thinking that it’s a math or stat formula that can add multiple numbers, but it’s way more than that. Outside the realm of lookup and reference formulas (i.e., XLOOKUP, VLOOKUP, and INDEX/MATCH), SUMPRODUCT is one of those functions that advanced Excel users love. You can read the following guide where I cover some basic and advanced usage of the INDEX/MATCH formula – INDEX & MATCH Functions Combo in Excel (10 Easy Examples) Also read: Formulas Not Working In Excel SUMPRODUCT Function While individually these functions aren’t that useful, when you combine them, it’s a powerful tool for advanced Excel formula users. There used to be a hot debate on which formula is better VLOOKUP vs INDEX/MATCH.Įven with XLOOKUP, I highly recommend you learn using INDEX/MATCH. Till the time XLOOKUP arrived, many advanced Excel users preferred using INDEX/MATCH over VLOOKUP (and this combination can take care of some of VLOOKUP’s drawbacks). So if you desire to learn about advanced formulas in Excel, you must learn how this magical combination works. The combination of INDEX and MATCH functions can do wonders.

#Microsoft excel formulas pdf how to

Use IFERROR with VLOOKUP to Get Rid of #N/A ErrorsĪlso read: How to Describe Excel Skills in a Resume? INDEX / MATCH Function.Avoid Nested IF Function in Excel…VLOOKUP to Rescue.Use VLookup to Get the Last Number in a List in Excel.How to Use VLOOKUP with Multiple Criteria.Here are some advanced formula examples of using VLOOKUP:

If you want to learn how to best use the VLOOKUP function, check out this detailed tutorial on this advanced formula: 10 VLOOKUP Examples For Beginner & Advanced Users There are many amazing things you can do with XLOOKUP, and I have covered some in the video below: It can scan the list, find out the position of the lookup value, and return the value from any corresponding column in the same row. When you identify an item you would want to order, you look to the right to check the prices.Īnd that’s what XLOOKUP does for you. so you get their menu and start scanning the menu from top to bottom. If you’re still confused about what this function does, consider this – you go to a newly opened café close to your house and you want to eat something. It also allows you to find the value, and then return any other value from the same row or column. You can use this function in situations where you need to scan a list of values in rows or columns and find out if the list contains the value you’re looking for (called the lookup value). XLOOKUP is a refined version of VLOOKUP, that addresses some of the drawbacks that VLOOKUP has. This means people using the earlier versions of Excel won’t be able to use it. It is a new function that is available in Excel 2021 and Excel for Microsoft 365. XLOOKUP is the king of advanced functions (Microsoft hasn’t given this title to any function, but I am sure no one deserves it more than this function). FIND / SEARCH Function (With Wildcard Characters).SUMIF/COUNTIF & SUMIFS/COUNTIFS Functions.