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 | |
| 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 | |
| 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 | |
| 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 | |
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 | =INDEX( C50:E50, 1.0, 2.0 ) | 8.0 | 9.0 | 10.0 | |
52 | 11.0 | =INDEX( F$52:G$53, C52, D52 ) | 1.0 | 1.0 | |
53 | 13.0 | =INDEX( F$52:G$53, C53, D53 ) | 1.0 | 2.0 | |
54 | 12.0 | =INDEX( F$52:G$53, 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 | =INDEX( C58:D58, 1.0, 1.0 ) | 12.0 | 14.0 | |
60 | 11.0 | =INDEX( F$60:G$61, C60, D60 ) | 1.0 | 1.0 | | 11.0 | 13.0 | |
61 | 14.0 | =INDEX( F$61:G$61, C61, D61 ) | 1.0 | 2.0 | | 12.0 | 14.0 | |
| A | B | C | D |
63 | !FE | =INDEX( E63:G63, D63, C63 ) | 2.0 | -1.0 | 10.0 | Excel says: #VALUE! |
64 | 11.0 | =INDEX( E64:G64, 0.0, C64 ) | 2.0 | |
65 | 11.0 | =INDEX( E65:G65, D65, C65 ) | 2.0 | 1.0 | 10.0 |
66 | !FE | ... | 2.0 | 2.0 | 10.0 | Excel says: #REF! |
68 | !FE | =INDEX( E$68:E$70, C68, D68 ) | 2.0 | -1.0 | 10.0 | Excel says: #VALUE! |
69 | 11.0 | =INDEX( E$68:E$70, C69, 0.0 ) | 2.0 | |
70 | 11.0 | =INDEX( E$68:E$70, C70, D70 ) | 2.0 | 1.0 | 12.0 |
71 | !FE | =INDEX( E$68:E$70, C71, D71 ) | 2.0 | 2.0 | | Excel says: #REF! |
73 | !FE | =INDEX( E$75:F$76, C73, 1.0 ) | -1.0 | | Excel says: #VALUE! |
74 | !FE | ... | 0.0 | | Excel says: #VALUE! |
75 | 11.0 | =INDEX( E$75:F$76, C75, 1.0 ) | 1.0 | |
76 | 12.0 | =INDEX( E$75:F$76, C76, 1.0 ) | 2.0 | |
77 | !FE | =INDEX( E$75:F$76, C77, 1.0 ) | 3.0 | | Excel says: #REF! |
| A | B | C | D | E | F | G | H | I |
82 | 12.0 | =LOOKUP( C82, D82:F82, G82:I82 ) | 20.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 |
83 | 11.0 | ... | 19.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 |
84 | 12.0 | ... | 21.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 |
85 | !NA | ... | 9.0 | 10.0 | 20.0 | 30.0 | 11.0 | 12.0 | 13.0 | 7.0 | Excel says: #N/A |
| A | B | C | D | E | F |
123 | 11.0 | =CHOOSE( C123, D123, E123, F123 ) | 1.0 | 11.0 | 12.0 | 13.0 | |
124 | 22.0 | ... | 2.0 | 21.0 | 22.0 | 23.0 | |
125 | 33.0 | ... | 3.0 | 31.0 | 32.0 | 33.0 | |
126 | !FE | ... | 4.0 | 11.0 | 12.0 | 13.0 | | Excel says: #VALUE! |
127 | !FE | ... | 0.0 | 11.0 | 12.0 | 13.0 | | Excel says: #VALUE! |
128 | 12.0 | ... | 2.99 | 11.0 | 12.0 | 13.0 | |