A large problem

Q: Each day I export a list of outstanding accounts receivable balances consisting of several thousand customers, and it is my job to produce a report containing the customers with the 25 highest outstanding balances for follow-up. As illustrated below, the problem is that the accounting system produces a list of the customers, balances, and days outstanding (columns A–C below), but I have a separate list of the A/R customer contact information (columns O–R) that must be merged into the final report. Right now I sort columns A–C by outstanding balance and copy the top 25 rows to my report area. I then hunt and peck for the corresponding 25 rows of contact information, copying and pasting each one separately to my report. I’m sure there is an easier solution. Can you please give me a clue?
 

A: Excel’s Large function, coupled with the Match, Index, and Vlookup functions, can help you get the job done quicker. You’ve already done a good job of setting up your worksheet layout; you just need to fill in a few formulas using these four steps.

1. Eliminate duplicates. To eliminate the possibility of two identical outstanding A/R balances, add a unique fractional amount to each outstanding A/R balance, as follows. Insert two new columns (columns E and F in the screenshot below) labeled Fractional and Amount+. Number the Fractional column E sequentially, starting with the value “1.” In the Amount+ column, create a formula adding the outstanding A/R balance from column B to a fractional amount using the Fractional column E as the numerator and the value “10,000” as the denominator (as shown below), then copy the formula down.

This measure eliminates any identical balances that would otherwise cause problems using the Match or Vlookup functions.

2. Extract 25 largest values. In cell J2, enter the function =LARGE($F$2:$F$101,H2) to extract and return the largest outstanding balance, then copy the function down to cells J3 through J26. The Large function searches the list of values you specify and returns the value that corresponds to the rank you specify. In the example below, the Large function in cell J2 looks in column F for the Nth largest value, based upon the function’s second attribute, which references the value “1” in cell H2.

As this function is copied downward, the second attribute changes each time to reference the second-, third-, fourth-highest values, and so on until a list of the top 25 values has been reported.

3. Extract company names. In cell I2, enter the function =MATCH(J2,F:F,0) to return the company name corresponding with the outstanding A/R amount (be sure to add the 0 (zero) attribute to the end of the function to force an exact match).

The Match function compares the value (now reported in column J) against the list of outstanding A/R values (in column F), and returns the row number where that value is positioned (row 33 corresponds with the value reported in cell J2 in this example).

Again in cell I2, edit the formula by adding the Index function, which uses the information returned by the Match function to fetch the company name from the 33rd row in column A. The completed formula containing the two nested functions appears as follows:

=INDEX(A:A,MATCH(J2,F:F,0))

Copy this formula downward to the next 24 rows.
 
Note A: Ordinarily, the Vlookup is easier to use, but because the lookup value (in column F) is not positioned to the left of the company name (in column A), we use the Match and Index functions instead to avoid the need to rearrange the source data.

Note B: Notice that without the addition of the fractional values in step 1, this formula would erroneously return the same company name for duplicate outstanding A/R balances, which is why step 1 is necessary.

4. Extract remaining data. The remaining report data can now be easily extracted from the second table array (in columns O through R) using a Vlookup function with the company name as the lookup criteria. Enter the functions =VLOOKUP($I2,$O:$R,2), =VLOOKUP($I2,$O:$R,3), and =VLOOKUP($I2,$O:$R,4) in cells K2, L2, and M2, respectively, and copy them down.

The final result is that the desired report is completed using data from the two table arrays. As the data in columns A–C are updated, so too is the report, ranked in order of the top 25 largest outstanding A/R balances. 

Note C: Ordinarily, I would position the second table array (columns O–R) on a separate worksheet. Instead, I positioned all data and calculations on the same worksheet for simplicity.

Note D: In the Match, Index, and Vlookup functions, I referenced entire columns rather than the data range (for simplicity), which assumes there is no other data in that column.

Download this Excel file (2013 or 2003 versions) at carltoncollins.com/large.xlsx or carltoncollins.com/large.xls.