Excel Tips

Is there Something More Flexible than CHOOSE? »

May 9, 2022

CHOOSE is strange in that it requires values such as 1, 2, 3. What if I need to check for values like 1, 7, 64?

I Don’t Want to Use a Lookup Table to Choose One of Five Choices »

May 6, 2022

I have to choose among five choices. I don’t want to nest a bunch of IF functions, and I really don’t want to add a lookup table off to the side of my worksheet. Is there a function that will allow me to specify the possible values in the function?

Embed a Small Lookup Table In Formula »

May 5, 2022

I have a small 5-row lookup table hidden out in column AA:AB. The sales reps who use the spreadsheet might inadvertently delete a row in their data, deleting the lookup table. Can I put the lookup table somewhere that they won’t destroy it?

Sum All of the Lookups »

May 4, 2022

Are there any other arcane tricks with the old LOOKUP function that you can use to close out this string of topics on lookup?

Return the Last Matching Value »

May 3, 2022

VLOOKUP returns the first match that it finds. I need to get the last match in the data. In this figure, I want to lookup A and find the 12 from row 5, since that is the latest data for A.

Return the Last Entry »

May 2, 2022

Someone has logged some data. For each group, data starts in row 5 and continues down for some number of rows. There are a different number of data points in each column. I need to get the last entry in each column.

Watch for Duplicates When Using VLOOKUP »

April 29, 2022

I used the VLOOKUP function to get sales from a second list into an original list, and then I received the next day’s sales in a file. When I use the MATCH function to find new customers, there is one new customer: Sun Life Fincl.

Combine Two Lists Using VLOOKUP »

April 28, 2022

I have a list of month-to-date sales by customer. My co-worker just sent me a list of sales for yesterday. I need to combine and merge these lists.

Combine Formulas into a Mega-Formula »

April 27, 2022

When I need to build a complex calculation, I sometimes need to build several intermediate formulas to help figure out the problem. When these formulas are all working, can I combine the logic from the intermediate formulas into a single formula?