Problem: I need to do a lookup where I find the product ID down the left side and the month from the top row. I need to return the intersection of that row and column.

Strategy: You can use a MATCH to find the row, a second MATCH to find the column, and then an IN- DEX to return the correct value.

In this example, the person using the spreadsheet uses the Validation dropdowns in J2 and J3 to select a product and month.

Figure 420 Select a product and month.

The lookup table has products in column A and months in row 1.

Figure 421 Find product A397 and Apr.

Your first formula will use MATCH to find the row within the table. Use =MATCH(J2,$A$2:$A$20,0). This is the same type of MATCH described in the previous three topics. The answer of 2 indicates that A397 is found in the second row of the lookup table.

The second formula will use MATCH to find the column within B1:G1. This means that MATCH can go both ways and essentially do an HLOOKUP. Use =MATCH(J3,B1:G1,0). The result of 4 indicates that Apr is in the fourth column of the lookup table.

Finally, use the INDEX function to return the value from row specified by the MATCH in J5 and from the column specified in J6. Use this formula: =INDEX(B2:G20,J5,J6).

Figure 422 The MATCH in J6 is like an HLOOKUP.

COMBINE FORMULAS INTO A MEGA-FORMULA

Problem: When I need to build a complex calculation, I sometimes need to build several intermediate for- mulas 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?

Strategy: Using temporary sub-formulas is a great way to figure out a somewhat complex formula. There is a relatively easy way to get all of these formulas back into a single formula.

Consider the previous example, a formula in J7 =INDEX(B2:G20,J5,J6) references two other cells J5 and J6. Each of those cells contain a formula.

Strategy: You can select characters in the formula bar and copy them to the Clipboard with Ctrl+C. When you copy an entire cell, you introduce many complexities, including the problem that you cannot paste this cell into the middle of a formula or into the Replace dialog. Instead, by copying characters from the formula bar, you have regular text on the Clipboard and can either paste into another formula or in the Replace dialog. Here’s what you do:

1. Select cell J5. In the formula bar, click the mouse after the equal sign and drag to the end of the formula. Press Ctrl+C to copy these characters to the Clipboard. Exit Edit mode by pressing the Esc key.

2. Select cell J7. In the formula bar, highlight the reference to J5

3. Press Ctrl+V to paste the formula from J5 to replace the reference to J5.

4. Select cell J6. Press F2, Ctrl+Shift+Home, Shift+Right Arrow, Ctrl+C, Esc. These keyboard short- cuts edit the cell, then select everything but the equals sign.

5. Select cell J7. Select the reference to J6

6. Press Ctrl+V to paste the formula from J6 to replace the reference to J6.

The result is a single formula that replaces the three formulas.

2

Figure 423 This is an intimidating formula once it is all combined.

Result: Your coworkers will be amazed at your ability to create massive formulas.

Alternate Strategy: Instead of following the steps just outlined, you can use the Replace dialog to combine the intermediate formulas into mega-formulas. Follow these steps:

1. Select cell J5. In the formula bar, use the mouse to select everything from immediately after the equals sign to the end of the formula. Press Ctrl+C to copy those characters to the Clipboard. Press the Esc key to exit the formula bar.

2. Select cells J7:J8. Gotcha: Make sure this selection contains two cells, even if you are only working on a single formula! If you select two or more cells, the Replace All command will work only within the selection. If you select only one cell, the Replace All command will extend to all 17 billion cells in the worksheet.

3. Select Home, Find & Select, Replace or Ctrl+H.

4. In the Find What box, type J5.

5. Tab to the Replace With box. Press Ctrl+V. Excel will copy the characters from the J5 formula into the dialog.

6. Click the Options button.

7. Make sure the Look In dropdown is set to Formulas. Make sure that Match Entire Cell Contents is unchecked. (If you start a new Excel session, both of these settings will be correct. However, the dialog remembers the settings from the last find and replace you did earlier in the current session, so it is always worth your time to click the Options button to make sure these settings are correct.)

8. Click Replace All. Excel will remove the reference to J5 from the selected cells and replace it with the characters from J5.

9. Repeat step 1 for cell J6.

10.Repeat steps 2–8.

Depending on how many times the intermediate formulas are referenced in the final formula, using Find and Replace might be faster than using the copy and paste method.

Gotcha: Be careful that your target formulas don’t contain references that contain some other form of B2 and C2, such as B20 or C210909. If your formulas do contain such references, when you replace B2, Excel will blindly put the B2 formula where the characters B2 appear in B20.

Problem: 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.

Figure 424 Combine these lists.

This is a 3-step process:

●● Do a MATCH or a VLOOKUP(,,1,) on the second list to find new customers. Add the new customers to the original list with previous sales of 0.

●● Now that the first list contains a superset of customers in either list, do a VLOOKUP on the first list to get the sales from the second list.

●● Add previous sales to new sales and convert to values. You can now delete the new list.

When you want to figure out if a customer in col- umn D is already in column A, most people will do

=VLOOKUP(D6,A6:A25,1,False). This will either show the customer name or #N/A. In this case, you are interested in the #N/A records.

However, now that you’ve read about MATCH, you can just as easily use MATCH to find which customers are in the other list. Use =MATCH(D6,$A$6:$A$25,0). In the figure below, all of the customers in column D have a match except Ford.

Any customers with #N/A are new customers and need to be added to the list of customers in column A. If you get a few #N/A values, sort by column F to bring the new customers together. Copy just the customer name and paste to the bottom of the list in column A. Enter $0 as the sales in column B for the new customers.

Figure 425 Ford is missing from the 1st list.

In the current example, only Ford is new, so you can copy Ford from D9 and paste to A26. Enter zero in B26. In real life, though, you will have a several customers who are new. Copy and paste below the first customer in A. Add zero to all the corresponding cells in B.

You’ve now completed the first of three steps. The next step is to add the real VLOOKUP to the first list.

=VLOOKUP(A6,$E$6:$F$16,2,FALSE) would return a mix of revenue values and #N/A errors. When an existing customer had no revenue on June 18, the result of the VLOOKUP will be #N/A. You can use the new IFERROR function to replace those #N/A values with zeroes.

Insert two new columns before column D. Label these temporary columns Addl Rev and New Total.

The formula in the new C6 is =IFERROR(VLOOKUP(A6,$F$6:$G$16,2,FALSE),0). Copy that formula down to row 26.

Figure 427 Add the old and new revenue.

Copy D6:D26. Select B6 and Paste Values.

Gotcha: Don’t worry that column D is showing a higher value than it should. Column D is still adding the current day’s revenue to the new total in B. You will be deleting column D very soon. If you don’t want column D to ever show a wrong value, you could copy D and paste values on top of itself first.

Change the date in A3 to indicate that the report has data through 6/18. Delete the temporary columns C through H.

It looks like the original data was sorted by descending revenue. Choose cell B6 and click the ZA button on the Data tab of the ribbon.

This process of comparing and combining two lists using VLOOKUP is a staple of data analysis. There is a faster and easier way to do this. See "Use a Pivot Table to Compare Two Lists" on page 362.