How To Use Lookup In Excel


Most know how to use a spreadsheet to do basic operations like add, subtract, multiply, and divide. We know some basic functions like =SUM and =AVERAGE. These simple operations are good for lots of useful spreadsheets. We can keep track of our checking account, track our gas mileage, keep a list of birthdates, maintain a database of names and addresses and email addresses, etc. 

It is that time of year once again. The date may have been moved to July 15th but the taxes still have to be paid. And for those who have investments and are over 70 and a half years old, there could be a minimum amount you are required to withdraw from those investments to pay an income tax on.

We can use a spreadsheet to make that calculation by just inputting the age and the amount of investment and the factor for our age. The factor table to use can be found online. Just google “RMD table 2020”.

 Vlookup

Using the VLOOKUP function we can create a spreadsheet with the table built-in so each year we simply enter the amount of our investment and it computes the RMD (Required Minimum Distribution).


The first parameter is the location of the lookup value. In this case, it is the age of the person. We could key this is each time. I used the DATEDIF function to calculate it from the date of birth. In this way, each year I only have to key in the current investment amount to find out the RMD. 

That formula is =DATEDIF(B5,NOW(),”Y”) and it uses the birthdate (DOB) and today’s date to come up with the current age.

Note: If your birthdate is between January 1 and the date you are running this calculation, adjust the formula to subtract a year since it is the age you were at the end of the tax year that needs to be used.  Change it to =DATEDIF(B5,NOW(),”Y”)-1

The correct factor is determined using =VLOOKUP(B6,A12:B56,2)


B6 is the cell containing the age. The Table starts in A12 and ends in B56.

Note: I only show the first seven ages in the screenshot.

The ”2″ indicates that the result will be found in the second column of the table.

Although I used this to demonstrate RMD calculations, VLOOKUP has many other uses. When teaching, I would use it to calculate letter grades for students based on their scores. I am sure you can think of many other uses as well.


This function is in Excel and Google Sheets and most other spreadsheet programs. Enjoy another tool from my toolbox.

Dick

About the Author

Dick Evans

Dick Evans has been in the tech industry for more than fifty years. Beginning his career in the '60s on IBM mainframes, he has been a Programmer, a Data Processing Manager (now called Information Systems Manager), a Consultant, an Assistant Professor, and a Writer. He has been published in a number of computer publications and has spoken at conferences here in the US and abroad. He is currently retired from the faculty at Rhode Island College and consulting/writing part-time, holding teaching seminars, and maintaining a number of Web sites. He maintains a blog with technical “stuff” and has a Web site at rwevans.com.

Leave a Reply

Your email address will not be published. Required fields are marked *