Figure 2.10 shows the data in the Excel file Monthly Product Sales Queries. Suppose we wish to design a simple query application to input the month and product name, and retrieve the corresponding sales. The three additional worksheets in the workbook show how to do this in three different ways. The Query1 worksheet (see Figure 2.11) uses the VLOOKUP function with embedded IF statements. The formulas in cell I8 is:

VLOOKUP(I5,A4:F15,IF(I6“A”,2,IF(I6“B”,3, IF(I6“C”,4,IF(I6“D”,5,IF(I6“E”,6))))),FALSE)

The IF functions are used to determine the column in the lookup table to use, and, as you can see, is somewhat complex, especially if the table were much larger.

The Query2 worksheet (not shown here; see the Excel workbook) uses the VLOOKUP and MATCH functions in cell I8. The formula in cell I8 is:

VLOOKUP(I5,A4:F15,MATCH(I6,B3:F3,0)1,FALSE)

In this case, the MATCH function is used to identify the column in the table corresponding to the product name in cell I6. Note the use of the “1” to shift the relative column number of the product to the correct column number in the lookup table.

Finally, the Query3 worksheet (also not shown here)

uses only INDEX and MATCH functions in cell I8. The formula in cell I8 is:

INDEX(A4:F15,MATCH(I5,A4:A15,0), MATCH(I6,A3:F3,0))

The MATCH functions are used as arguments in the INDEX function to identify the row and column numbers in the table based on the month and product name. The INDEX function then retrieves the value in the corresponding row and column. This is perhaps the cleanest formula of the three.

By studying these examples carefully, you will better understand how to use these functions in other applications.