Lookup Functions In AFC
The supported lookup functions are shown by example below. Note that AFC sometimes returns zero (0.0
) where Excel will return #VALUE
, #N/A
, or #REF!
.
| A | B | C | D | E | F |
2 | !NA | =MATCH(C2,D2:F2,1.0) | 99.0 | 100.0 | 200.0 | 500.0 | | Excel says: #N/A |
3 | 1.0 | ... | 100.0 | 100.0 | 200.0 | 500.0 | |
4 | 1.0 | ... | 101.0 | 100.0 | 200.0 | 500.0 | |
5 | 1.0 | ... | 199.0 | 100.0 | 200.0 | 500.0 | |
6 | 2.0 | ... | 200.0 | 100.0 | 200.0 | 500.0 | |
7 | 2.0 | ... | 201.0 | 100.0 | 200.0 | 500.0 | |
8 | 3.0 | ... | 500.0 | 100.0 | 200.0 | 500.0 | |
9 | 3.0 | ... | 501.0 | 100.0 | 200.0 | 500.0 | |
10 | 2.0 | =MATCH(C10,D10:F10) | 25.0 | 10.0 | 20.0 | 30.0 | |
11 | 1.0 | ... | 15.0 | 10.0 | 20.0 | 30.0 | |
10 | 2.0 | ... | 25.0 | 10.0 | 20.0 | 30.0 | |
| A | B | C | D | E | F |
13 | 3.0 | =MATCH(C13,D13:F13,-1.0) | 99.0 | 500.0 | 200.0 | 100.0 | |
14 | 3.0 | ... | 100.0 | 500.0 | 200.0 | 100.0 | |
15 | 2.0 | ... | 101.0 | 500.0 | 200.0 | 100.0 | |
16 | 2.0 | ... | 199.0 | 500.0 | 200.0 | 100.0 | |
17 | 2.0 | ... | 200.0 | 500.0 | 200.0 | 100.0 | |
18 | 1.0 | ... | 201.0 | 500.0 | 200.0 | 100.0 | |
19 | 1.0 | ... | 500.0 | 500.0 | 200.0 | 100.0 | |
20 | !NA | ... | 501.0 | 500.0 | 200.0 | 100.0 | | Excel says: #N/A |
21 | 1.0 | ... | 25.0 | 30.0 | 20.0 | 10.0 | |
22 | 2.0 | ... | 15.0 | 30.0 | 20.0 | 10.0 | |
21 | 1.0 | ... | 25.0 | 30.0 | 20.0 | 10.0 | |
| A | B | C | D | E | F |
24 | !NA | =MATCH(C24,D24:F24,0.0) | 99.0 | 100.0 | 200.0 | 500.0 | | Excel says: #N/A |
25 | 1.0 | ... | 100.0 | 100.0 | 200.0 | 500.0 | |
26 | !NA | ... | 101.0 | 100.0 | 200.0 | 500.0 | | Excel says: #N/A |
27 | !NA | ... | 199.0 | 100.0 | 200.0 | 500.0 | | Excel says: #N/A |
28 | 2.0 | ... | 200.0 | 100.0 | 200.0 | 500.0 | |
29 | !NA | ... | 201.0 | 100.0 | 200.0 | 500.0 | | Excel says: #N/A |
30 | 3.0 | ... | 500.0 | 100.0 | 200.0 | 500.0 | |
31 | !NA | ... | 501.0 | 100.0 | 200.0 | 500.0 | | Excel says: #N/A |
32 | !NA | ... | 25.0 | 10.0 | 20.0 | 30.0 | | Excel says: #N/A |
33 | 2.0 | ... | 20.0 | 10.0 | 20.0 | 30.0 | |
34 | 2.0 | ... | 20.0 | 30.0 | 20.0 | 10.0 | |
32 | !NA | ... | 25.0 | 10.0 | 20.0 | 30.0 | | Excel says: #N/A |
| A | B | C | D | E | F |
37 | 6.0 | =INDEX(C37:E37,2.0) | 5.0 | 6.0 | 7.0 | |
38 | 9.0 | ... | 8.0 | 9.0 | 10.0 | |
37 | 6.0 | ... | 5.0 | 6.0 | 7.0 | |
40 | 5.0 | =INDEX(D40:F40,C40) | 2.0 | 4.0 | 5.0 | 6.0 | |
41 | 8.0 | ... | 3.0 | 6.0 | 7.0 | 8.0 | |
42 | 6.0 | ... | 1.0 | 6.0 | 7.0 | 8.0 | |
43 | !FE | ... | 0.0 | 6.0 | 7.0 | 8.0 | | Excel says: #VALUE! |
44 | !FE | ... | 4.0 | 6.0 | 7.0 | 8.0 | | Excel says: #REF! |
46 | 0.0 | ... | 2.0 | | | | |
47 | 5.0 | ... | 2.0 | | 5.0 | | |
| A | B | C | D | E | F | G |
49 | 9.0 | =INDEX(C49:E50,2.0,2.0) | 5.0 | 6.0 | 7.0 | |
50 | 9.0 | ... | 8.0 | 9.0 | 10.0 | |
49 | 9.0 | ... | 5.0 | 6.0 | 7.0 | |
52 | 11.0 | =INDEX(F52:G53,C52,D52) | 1.0 | 1.0 | |
53 | 13.0 | =INDEX(F52:G53,C53,D53) | 1.0 | 2.0 | |
54 | 12.0 | =INDEX(F52:G53,C54,D54) | 2.0 | 1.0 | |
55 | 14.0 | ... | 2.0 | 2.0 | |
57 | 11.0 | =INDEX(C57:D58,1.0,1.0) | 11.0 | 13.0 | |
58 | 12.0 | ... | 12.0 | 14.0 | |
57 | 11.0 | ... | 11.0 | 13.0 | |
60 | 11.0 | =INDEX(F60:G61,C60,D60) | 1.0 | 1.0 | | 11.0 | 13.0 | |
61 | 14.0 | ... | 1.0 | 2.0 | | 12.0 | 14.0 | |
60 | 11.0 | ... | 1.0 | 1.0 | | 11.0 | 13.0 | |
64 | !FE | =INDEX(E66:F67,C64,1.0) | -1.0 | | Excel says: #VALUE! |
65 | !FE | ... | 0.0 | | Excel says: #VALUE! |
66 | 11.0 | =INDEX(E66:F67,C66,1.0) | 1.0 | |
67 | 12.0 | =INDEX(E66:F67,C67,1.0) | 2.0 | |
68 | !FE | =INDEX(E66:F67,C68,1.0) | 3.0 | | Excel says: #REF! |
| A | B | C | D | E | F | G | H | I |
73 | 12.0 | =LOOKUP(C73,D73:F73,G73:I73) | 20.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 |
74 | 11.0 | ... | 19.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 |
75 | 12.0 | ... | 21.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 |
76 | !NA | ... | 9.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 | Excel says: #N/A |
73 | 12.0 | ... | 20.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 |
| A | B | C | D | E | F |
114 | 11.0 | =CHOOSE(C114,D114,E114,F114) | 1.0 | 11.0 | 12.0 | 13.0 | |
115 | 22.0 | ... | 2.0 | 21.0 | 22.0 | 23.0 | |
116 | 33.0 | ... | 3.0 | 31.0 | 32.0 | 33.0 | |
117 | !FE | ... | 4.0 | 11.0 | 12.0 | 13.0 | | Excel says: #VALUE! |
118 | !FE | ... | 0.0 | 11.0 | 12.0 | 13.0 | | Excel says: #VALUE! |
119 | 12.0 | ... | 2.99 | 11.0 | 12.0 | 13.0 | |