The big deal is the INDEX formula is simply AWESOME! Put it together with MATCH and you have an even powerful formula which I call the INDEX + MATCH combo. Any budding data analysts out there I urge you to learn INDEX + MATCH combo – it’s powerful and nimble. Actually I am going to …
This is a short PS post to my earlier post on using Array Constants in Formulas basically to simplify them. The focus of the earlier post was avoiding Nested IFs but what about simplifying OR logic situations. Try… =OR(B5={“A”,”B”,”D”}) will return TRUE if B5 = A or B or D. This is a lot simpler …
Update 12/12/2021 Since writing this post in 2017 Validation Dropdown lists based on Table Columns ranges, Excel now has new revolutionary dynamic array formulas that really has overtaken all the workarounds for better dropdown lists we used to do such as described in my post below. Now the new formula SORT() + UNIQUE() in Excel …
Validation Dropdown lists based on Table Columns ranges Read More »
This post is about monitoring the workbook environment when working on key workbooks or workbook applications especially if they involve VBA, which is my approach to developing key workbooks for clients. You wouldn’t do this for an ad hoc or quick and dirty workbook. But for workbooks you or your clients are going to be …
Update 12/12/2021 Since writing this post in 2017, Excel now has new revolutionary dynamic array formulas that really has overtaken all the workarounds for better dropdown lists we used to do such as described in my post below. Now the new formula SORT() + UNIQUE() in Excel Office 365 (although not in earlier stand alone …
No more gaps – a formula to remove spaces in lists or empty rows in ranges Read More »
This is a very simple, but I think very practical little tip. You need to convert a formula to values in cell or a range of cells to values. I don’t know about you, but I do this all time. I use formulas to manipulate text and numbers to save time then I just want …
Array constants can help simplify your formulas. I find I use this kind of formula a lot… =IF(AND(B5<>{“A”,”B”,”C”,”D”}),”Yes”,”No”) Instead of: =IF(AND(B5<>”A”,B5<>”B”,B5<>”C”,B5<>”D”),”Yes”,”No”) … and imagine if you want to test 20 items for B5 is not equal to? It’s shorter and easier to read and edit. I discovered this by just playing around way back but …
Somewhere along the line, I realised I was better off using what I call the INDEX / MATCH combo in nearly all lookup situations and not the ever popular VLOOKUP. The INDEX /MATCH combo can look left and right (unlike VLOOKUP which can only look right), you don’t have to have sorted data to lookup …
Why I like INDEX / MATCH combo (and not VLOOKUP) Read More »
Nested IFs. We have all done them but there comes a time to find a better way. In Excel 2003 you were limited to 7 levels of nesting but 2007 onwards the level could be increased to 64. Both these levels are beyond my pain barrier for an overly complex formula fraught with danger. Even …