Daily posts of Excel tips…and other stuff
Archive for the ‘List and Table Functions’ Category.
Here are three ways to lookup a value based on two columns. Let’s start with this random data:
And let’s say that we want to lookup the autonumber value for Carlo. Not the Carlo in Seaford, but the Carlo in Westwood.
SUMPRODUCT
With ‘Carlo’ in E2 and ‘Westwood’ in F2, use =SUMPRODUCT(($A$2:$A$16=E2)*($B$2:$B$16=F2)*(C2:C16))
If you have more [...]
As you know, the fourth argument of the VLOOKUP worksheet function determines whether VLOOKUP finds an exact match or an approximate match. If you set the last argument to False, and there is no exact match in the lookup list, the formula will return #N/A!.
In Excel 2003, the best way to capture this error, [...]
It’s college football bowl season once again. I have a spreadsheet I use to help me identify winners and losers (for entertainment purposes only).
The four yardage columns are yards per game for rushing offense, rushing defense, passing offense, and passing defense, respectively. I’m trying to identify teams that have better defenses (because defense [...]
Excel provides an easy way to create a table of hyperlinks and even save the workbook as a web page. The built-in method for creating a hyperlink is Insert > Hyperlink (Ctrl+K). The problem with this method is mass changes, like renaming your server. Editing each one (if you have a lot) [...]
Red wants to have a kind-of lottery for his students. He will award them prizes based on a random drawing, but wants to weight each student based on the number of assignments turned in. Normally, I would accomplish this by typing the name of each student in column A one time for every [...]
Hi all,
Today I published my first article dedicated to Excel 2007 on my site.
The article is about Excel 2007’s new Table feature and is aimed at the beginner Excel 2007 user (that would be just about everyone except John Walkenbach ).
From the intro:
With the release of Excel 2007, Microsoft has introduced a new [...]
Prevent #REF! errors from creeping into your running total columns.
Tomkat is trying to cap mileage paid based on production. The mileage cap varies based on production and site. Read this newsgroup post for details if you like. This newsgroup post is where it all started.
This calls for a VLOOKUP/MATCH combo if I ever saw it.
The formula in E15 (and filled down [...]
