[ Pobierz całość w formacie PDF ]
.Figure 12-6 shows a worksheet with a lookup table that contains employee numbers(column C) and employee names (column D).The lookup table is named EmpList.The formula in cell B2, which follows, looks up the employee number entered in cellB1 and returns the corresponding employee name:=VLOOKUP(B1,EmpList,2,FALSE)Figure 12-6: This lookup table requires an exact match.Because the last argument for the VLOOKUP function is FALSE, the function returnsa value only if an exact match is found.If the value is not found, the formula returns#N/A.This, of course, is exactly what you want to happen because returning anapproximate match for an employee number makes no sense.Also, notice that theemployee numbers in column C are not in ascending order.If the last argument forVLOOKUP is FALSE, the values need not be in ascending order.TipIf you prefer to see something other than #N/Awhen the employee number is notfound, you can use an IF function to test for the #N/Aresult (using the ISNA func-tion) and substitute a different string.The following formula displays the text NotFound rather than #N/A:=IF(ISNA(VLOOKUP(B1,EmpList,2,FALSE)), Not Found ,VLOOKUP(B1,EmpList,2,FALSE))Looking up a value to the leftThe VLOOKUP function always looks up a value in the first column of the lookuprange.But what if you want to look up a value in a column other than the first col-umn? It would be helpful if you could supply a negative value for the third argumentfor VLOOKUP but you can t. 15 539671 ch12.qxd 8/28/03 10:01 AM Page 259Chapter 12 &' Creating Formulas That Look Up Values259Figure 12-7 illustrates the problem.Suppose you want to look up the batting average(column B, in a range named Averages) of a player in column C (in a range namedPlayers).The player you want data for appears in a cell named LookupValue.TheVLOOKUP function won t work because the data is not arranged correctly.Oneoption is to rearrange your data, but sometimes that s not possible.Figure 12-7: The VLOOKUP function can t look up a value in column B,based on a value in column C.One solution is to use the LOOKUP function, which requires two range arguments.The following formula (in cell F3) returns the batting average from column B of theplayer name contained in the cell named LookupValue:=LOOKUP(LookupValue,Players,Averages)Using the VLOOKUP function requires that the lookup range (in this case, thePlayers range) is in ascending order.In addition to this limitation, the formula suf-fers from a slight problem: If you enter a nonexistent player (in other words, theLookupValue cell contains a value not found in the Players range), the formulareturns an erroneous result.A better solution uses the INDEX and MATCH functions.The formula that followsworks just like the previous one except that it returns #N/A if the player is notfound.Another advantage is that the player names need not be sorted.=INDEX(Averages,MATCH(LookupValue,Players,0))On theYou can access a workbook that demonstrates both of the formulas in thisCD-ROMsection on the companion CD-ROM. 15 539671 ch12.qxd 8/28/03 10:01 AM Page 260Part II &' Working with Formulas and Functions260Performing a case-sensitive lookupThe Excel lookup functions (LOOKUP, VLOOKUP, and HLOOKUP) are not case sensi-tive.For example, if you write a lookup formula to look up the text budget, the formulaconsiders any of the following a match: BUDGET, Budget, or BuDgEt.Figure 12-8 shows a simple example.Range D2:D7 is named Range1, and range E2:E7is named Range2.The word to be looked up appears in cell B1 (named Value).Figure 12-8: Using an array formula to performa case-sensitive lookup.On theYou can access the workbook shown in Figure 12-8 on the companion CD-ROM.CD-ROMThe array formula that follows is in cell B2.This formula does a case-sensitivelookup in Range1 and returns the corresponding value in Range2.{=INDEX(Range2,MATCH(TRUE,EXACT(Value,Range1),0))}The formula looks up the word DOG (uppercase) and returns 300.The followingstandard LOOKUP formula (which is not case sensitive) returns 400:=LOOKUP(Value,Range1,Range2)NoteWhen entering an array formula, remember to use Ctrl+Shift+Enter.Choosing among multiple lookup tablesYou can, of course, have any number of lookup tables in a worksheet.In somecases, your formula may need to decide which lookup table to use.Figure 12-9shows an example. 15 539671 ch12.qxd 8/28/03 10:01 AM Page 261Chapter 12 &' Creating Formulas That Look Up Values261Figure 12-9: This worksheet demonstrates the use of multiple lookup tables.This workbook calculates sales commission and contains two lookup tables: G3:H9(named Table1) and J3:K8 (named Table2).The commission rate for a particular salesrepresentative depends on two factors: the sales rep s years of service (column B)and the amount sold (column C).Column D contains formulas that look up the com-mission rate from the appropriate table.For example, the formula in cell D2 is=VLOOKUP(C2,IF(B20,(((C6-C5)/ If the payment is positive, the formula calculates the365)*H5)*APR,0) interest.If the payment is negative (an addition to theloan), the formula displays 0.E6 =B6-D6 The formula subtracts the interest amount from thepayment to calculate the amount credited to principal.F6 =IF(B6>0,F5+B6,F5) If the payment is positive, the formula adds thepayment to the running total
[ Pobierz całość w formacie PDF ]
