汇集一些查找方法.xls

1、Base1122 a100105 b101106 c102107 d103108 e104109 a3333110110 110 33334455 110110115120 111116121 112117122 113118123 114119124 =INDEX(F8:K13,MATCH(F16,F8:F13,0),MATCH(G16,F8:K8,0) 1a 2b 3 3c 4d 5e 6f 7g c3 3 3=VLOOKUP(F40,CHOOSE(1,2,G32:G38,F32:F38),2,0) 3INDEX(F32:F38,MATCH(F40,G32:G38,0) =VLOOKUP(

2、F40,CHOOSE(1,2,G32:G38,F32:F38),2,0) INDEX(F32:F38,MATCH(F40,G32:G38,0) Stephen10 Chip20 Tom30 John40 Rob50 Stephen60 Chip7070 Tom80 John90 Rob100 Stephen110 Chip120120 Tom130 John140 Rob150 Chip3 3120=VLOOKUP(C21,CHOOSE(1,2,COUNTIF(OFFSET(B1,ROW(B1:B19),B21),C1:C19),2,0) 120 Last120120120=LOOKUP(2,

3、1/(B5:B19=B21),C5:C19) =VLOOKUP(C21,CHOOSE(1,2,COUNTIF(OFFSET(B1,ROW(B1:B19),B21),C1:C19),2,0) TypeAmountSexCostPurchased Cat1Male$5.005/22/01 Cat2Male$5.001/15/00 Dog3Male$20.004/25/02 Rat5Female$1.008/15/02 Mouse1Female$1.001/1/00 Bird5Male$10.001/5/00 Horse1Female$100.006/9/02 Dog2Male$150.005/24

4、/02 Horse8Male$200.002/23/02 Cat9Female$100.003/24/02 Dog6Male$200.004/14/02 Formula=FindNth(A1:E12,“Dog“,2,“Male“,3,5) Result5/24/022002/5/24 =INDEX(E:E,SMALL(IF(A2:A12&“-“&C2:C12=“Dog“&“-“&“Male“,ROW(A2:A12),2) 查詢 A1:E12 中第 2 次出現 “Dog“ 及 第 3 欄是“Male“ ，第 5 欄的數據。 =INDEX(E:E,SMALL(IF(A2:A12&“-“&C2:C1

5、2=“Dog“&“-“&“Male“,ROW(A2:A12),2) 910 152 186 6 64 368 8 12 187 7 2 24 48 8 Not Under 2 21 16 6 Not Over 3 这个偶没有想到其他的方法，大家一起想想，？！ Apolloh 2 23 3 Row2 24 42 21 1 Column2 24 4 2 21 1 Since this formula returns two values, you must enter it into an array of two cells. First select the two cells which y

6、ou want to contain the results, and then type the formula. Be sure to press Ctrl+Shift+Enter rather than just Enter when you enter the formula. Because this formula is in an array of cells, you wont be able to edit each cell independently. To change the formula, you must select both cells, edit the

7、formula, and then press Ctrl+Shift+Enter when youre done. 当数组公式返回结果为两个值时，你需要在两个单元格输入公式。先选择你希望记过显示的2个单元格，然后输入公式，记得按结束公式的输入，而不是简单得用结束。因为这是多单元格数组公式，你不能直接编辑其中的一个单元。必须先选择所有单元，然后修改公式，再按结束。 这个偶没有想到其他的方法，大家一起想想，？！ Apolloh Since this formula returns two values, you must enter it into an array of two cells. F

8、irst select the two cells which you want to contain the results, and then type the formula. Be sure to press Ctrl+Shift+Enter rather than just Enter when you enter the formula. Because this formula is in an array of cells, you wont be able to edit each cell independently. To change the formula, you

9、must select both cells, edit the formula, and then press Ctrl+Shift+Enter when youre done. 当数组公式返回结果为两个值时，你需要在两个单元格输入公式。先选择你希望记过显示的2个单元格，然后输入公式，记得按结束公式的输入，而不是简单得用结束。因为这是多单元格数组公式，你不能直接编辑其中的一个单元。必须先选择所有单元，然后修改公式，再按结束。 Since this formula returns two values, you must enter it into an array of two cells.

10、 First select the two cells which you want to contain the results, and then type the formula. Be sure to press Ctrl+Shift+Enter rather than just Enter when you enter the formula. Because this formula is in an array of cells, you wont be able to edit each cell independently. To change the formula, yo

11、u must select both cells, edit the formula, and then press Ctrl+Shift+Enter when youre done. 当数组公式返回结果为两个值时，你需要在两个单元格输入公式。先选择你希望记过显示的2个单元格，然后输入公式，记得按结束公式的输入，而不是简单得用结束。因为这是多单元格数组公式，你不能直接编辑其中的一个单元。必须先选择所有单元，然后修改公式，再按结束。 Since this formula returns two values, you must enter it into an array of two cell

12、s. First select the two cells which you want to contain the results, and then type the formula. Be sure to press Ctrl+Shift+Enter rather than just Enter when you enter the formula. Because this formula is in an array of cells, you wont be able to edit each cell independently. To change the formula,

13、you must select both cells, edit the formula, and then press Ctrl+Shift+Enter when youre done. 当数组公式返回结果为两个值时，你需要在两个单元格输入公式。先选择你希望记过显示的2个单元格，然后输入公式，记得按结束公式的输入，而不是简单得用结束。因为这是多单元格数组公式，你不能直接编辑其中的一个单元。必须先选择所有单元，然后修改公式，再按结束。 Since this formula returns two values, you must enter it into an array of two ce

14、lls. First select the two cells which you want to contain the results, and then type the formula. Be sure to press Ctrl+Shift+Enter rather than just Enter when you enter the formula. Because this formula is in an array of cells, you wont be able to edit each cell independently. To change the formula, you must select both cells, edit the formula, and then press Ctrl+Shift+Enter when youre done. 当数组公式返回结果为两个值时，你需要在两个单元格输入公式。先选择你希望记过显示的2个单元格，然后输入公式，记得按结束公式的输入，而不是简单得用结束。因为这是多单元格数组公式，你不能直接编辑其中的一个单元。必须先选择所有单元，然后修改公式，再按结束。