MSOfficeExcel 2007 Tutorial Lookup Reference Functions 63 codes

1 =ADDRESS(2,3) returns Absolute reference

2 =ADDRESS(2,3,1,FALSE,[Book1]Sheet1) return absolute reference to another workbook and worksheet

3 =ADDRESS(2,3,1,FALSE,EXCEL SHEET) return absolute reference to another worksheet

4 =ADDRESS(2,3,2) return absolute row; relative column

5 =ADDRESS(2,3,2,FALSE) return absolute row; relative column in R1C1 reference style

6 =AREAS(B2

7 =CHOOSE(2, One, Two, Three)

8 =CHOOSE(4,B2,B3,B4,B5)

9 =COLUMN() returns column in which the formula appears

10 =COLUMN(D3

11 =COLUMN(D3)

12 =COLUMN(Scores) return the first column of the Scores range

13 =COLUMNS({1,2,3;4,5,6}) returns the number of columns in the array constant

14 =FIND(birthday,A1)

15 =FIND(y,A1,10)

16 =HLOOKUP(Name,A1

17 =HLOOKUP(Salary,A1

18 =INDEX((A1

19 =INDEX({1,2;3,4},0,2) returns the value in the first row, second column in the array constant

20 =INDEX(A2

21 =INDEX(B1

22 =INDIRECT($A$3) get the value of the reference in cell A3

23 =INDIRECT(B&$A$5) returns the value of a reference in cell A5

24 =LOOKUP(0,A2

25 =LOOKUP(5,A2

26 =LOOKUP(6 66,A2

27 =LOOKUP(A3,{3,6,7,8,9},{F,D,C,B,A})

28 =LOOKUP(A4,{0,70,73,77,80,83,87,90,93,97},{F,D+,C-,C,C+,B-,B,B+,A-,A,A+})

29 =LOOKUP(abcd,{a,1;b,2;c,3})

30 =MATCH(0,A2

31 =MATCH(3,A2

32 =MATCH(8,A2

33 =OFFSET(A3,0,-2)

34 =OFFSET(A3,-2,0)

35 =OFFSET(C3

36 =OFFSET(C3,2,3,1,1) displays the value in cell F5

37 =ROW() returns the row number in which the formula appears

38 =ROW(C4

39 =ROW(Scores) returns the first row of the Scores range

40 =SUM(B2

41 =SUM(CHOOSE(2,B1

42 =SUM(INDEX(A1

43 =SUM(OFFSET (A3,1,2,50,1))

44 =SUM(OFFSET(C3

45 =VLOOKUP( 7,A2

46 =VLOOKUP(0 1,A2

47 =VLOOKUP(1,A1

48 Always refer to cell A10

49 AREAS returns the number of areas in a reference

50 CHOOSE(index_num,value1,value2, ) chooses a value from a list of values

51 COLUMN(reference) returns the column number of a reference

52 COLUMNS returns the number of columns in an array or reference

53 INDEX Uses an index to choose a value from a reference or array

54 INDIRECT(ref_text,a1) returns a reference indicated by a text value

55 LOOKUP looks up values from a one-row or one-column range or an array

56 LOOKUP(lookup_value,array)

57 MATCH(lookup_value,lookup_array,match_type) returns the relative position of an item in an array

58 OFFSET(reference,rows,cols,height,width) returns a reference offset from a given reference

59 Return a worksheet column letter (ranging from A to XFD) for the value contained in cell A1

60 ROW(reference) returns the row number of a reference

61 ROWS(array) returns the number of rows in a reference

62 To specify several references as a single argument, include extra sets of parentheses

63 TRANSPOSE(array) returns the transpose of an array