MrExcel Excel Videos

VLOOKUP when used with False is a slow function Sorting the data AZ does not speed up the function Sorting by popularity could speed up the function Switching to VLOOKUP with True is faster, but it will report the wrong answer if the item is not found To mitigate the problem, do a VLOOKUP(A2,Table,1,True) to see if the result is A2 first 14000 VLOOKUP(True) and 7000 IF run faster than 7000 VLOOKUP(False)

With a tiered commission, bonus, or discount program, you often have to nest your IF functions The Excel 2003 limit was 7 nested IF statements. You can now nest 32, but I don't think you should ever nest 32 When would you ever use the approximate match version of VLOOKUP? This is the time. Translate the discount program into a lookup table VLOOKUP won't find the answer in most cases. Putting ,True at the end will tell VLOOKUP to find the value just less. This is the only time the VLOOKUP table has to be sorted. Don't want the VLOOKUP table off to the side? Embed it in the formula. F2 to edit the formula. Select the lookup table. Press F9. Enter.

VLOOKUP can not look to the left of the key field! Use MATCH to find where the item is located, then use INDEX to retrieve the lookup value. Or - in real life, rearrange the lookup table This episode features an original letterpress poster from Bobby Rosenstock at justajar.com

Say that you have to do 12 columns of VLOOKUP Carefully use a single dollar sign before the column of the lookup value Carefully use four dollar signs for the lookup table You are still hard-coding the third column argument. One common solution is to add a row of helper cells with the column number. Another less-efficient solution is to use COLUMN(B2) inside the VLOOKUP formula. But, doing 12 VLOOKUP for each row is very inefficient Instead, add a helper column with a heading of WHERE and do a single Match. The MATCH takes as long as the VLOOKUP for January. You can then use 12 INDEX functions. These are incredibly fast compared to VLOOKUP. The INDEX will point to a single column of answers with $ before the rows. The INDEX will point...

VLOOKUP solves many problems Common VLOOKUP problems: If VLOOKUP starts out working, but #N/A becomes more prominent: forgot $ in lookup table A few #N/A: items missing from the table None of the VLOOKUP work: check for trailing spaces Remove trailing spaces with TRIM Numbers and numbers stored as text Select both columns and use Alt+DEF Episode includes a joke that both accountants and IT people find funny, but for different reasons

There are a secret class of formulas called Array Formulas. An array formula can do thousands of intermediate calculations. They often require you to press Ctrl+Shift+Enter, but not always. The best book on array formulas is Mike Girvin's Ctrl+Shift+Enter. INDIRECT lets you use concatenation to build something that looks like a cell reference. Dates are nicely formatted but are stored as a number of days since January 1 1900. Concatenating two dates will point to a range of rows in Excel. Asking for the ROW(INDIRECT(Date1:Date2)) will "pop out" an array of many consecutive numbers Using the WEEKDAY function to figure out if a date is Friday. How many Fridays occur in this July? To watch a formula calculate in slow motion, use the...

Three ways to handle multiple conditions in an IF function. Episode recap: The simplest IF function is =IF(Logical Test,Formula if True, Formula if False) But what to you do if you have to test two conditions? Many people will do =IF(Test 1, IF(Test 2, Formula if True, False), False) This gets unwieldy if 3, 5, 17 conditions! Instead, use =IF(AND(t1,t2,t3,t4),Formula if True, Formula if False) If you like AND, consider OR, NOT for other situations NAND can be done with NOT(AND()) NOR can be done with NOT(OR()) Be careful when using XOR as the results are not what you expect

Yesterday in podcast 2023 I used NETWORKDAYS and NETWORKDAYS.INTL There is a similar pair of functions WORKDAY and WORKDAY.INTL This function takes a start date, then a number of days, weekend type and holidays and calculates the end date. For example, calculate when a 30 work-day probation period might end. But a more common use might be to build an employee schedule or employee calendar Put the first start date. Then add 1 workday using WORKDAY or WORKDAY.INTL. Drag that formula down to build the schedule.

Calculate work days between two dates excluding weekends and holidays. This episode also handles 6-day workweeks, countries where the weekend is Friday and Saturday, plus businesses like barber shops or farm markets that have operating hours on Monday, Thursday, Friday, Saturday. Episode Recap: Date math in Excel: Subtract earlier date from later date + 1 To ignore weekends, use NETWORKDAYS function To not count holidays, use the 3rd argument in NETWORKDAYS For non-standard weekends, use NETWORKDAYS.INTL Secret 7-binary digit code for work weeks that are not consecutive days Alt+ESF for Paste Special Formulas