Home | Quick Start | Documentation | Download | Support | Contribute

# Lookup Functions In AFC

### Lookup Functions

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!`.

##### MATCH (ascending)
 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

##### MATCH (descending)
 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

##### MATCH (equality)
 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

##### INDEX (1-dim)
 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

##### INDEX (2-dim)
 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

##### INDEX (1-dim, 2-idx)
 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!

##### INDEX (return string)
 A B C D E 79 one =INDEX( D79:E79, C79 ) 1.0 one two 80 two ... 2.0 one two

##### LOOKUP (vectors horiz.)
 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

##### LOOKUP (vectors vert.)
 A B C D E 87 11.0 =LOOKUP( C87, D\$87:D\$89, E\$87:E\$89 ) 19.0 10.0 11.0 88 12.0 =LOOKUP( C88, D\$87:D\$89, E\$87:E\$89 ) 20.0 20.0 89 12.0 =LOOKUP( C89, D\$87:D\$89, E\$87:E\$89 ) 21.0 30.0 90 !NA =LOOKUP( C90, D\$87:D\$89, E\$87:E\$89 ) 9.0 Excel says: #N/A

##### LOOKUP (array horiz)
 A B C D E F G 92 22.0 =LOOKUP( C92, D\$92:G\$94 ) 29.0 10.0 20.0 30.0 40.0 93 23.0 =LOOKUP( C93, D\$92:G\$94 ) 30.0 11.0 94 23.0 =LOOKUP( C94, D\$92:G\$94 ) 31.0 21.0

##### LOOKUP (array square)
 A B C D E F 96 21.0 =LOOKUP( C96, D\$96:F\$98 ) 19.0 10.0 11.0 21.0 97 22.0 =LOOKUP( C97, D\$96:F\$98 ) 20.0 20.0 98 22.0 =LOOKUP( C98, D\$96:F\$98 ) 21.0 30.0

##### LOOKUP (array vert)
 A B C D E F 100 21.0 =LOOKUP( C100, D\$100:F\$103 ) 19.0 10.0 11.0 21.0 101 22.0 =LOOKUP( C101, D\$100:F\$103 ) 20.0 20.0 102 22.0 ... 21.0 30.0 103 23.0 =LOOKUP( C103, D\$100:F\$103 ) 30.0 40.0

##### HLOOKUP
 A B C D E F G 105 22.0 =HLOOKUP( C105, D\$105:G\$107, 3.0 ) 29.0 10.0 20.0 30.0 40.0 106 23.0 =HLOOKUP( C106, D\$105:G\$107, 3.0 ) 30.0 11.0 107 23.0 =HLOOKUP( C107, D\$105:G\$107, 3.0 ) 31.0 21.0 108 20.0 =HLOOKUP( C108, D\$105:G\$107, D108 ) 29.0 1.0 109 13.0 ... 30.0 2.0 110 23.0 ... 31.0 3.0 111 !FE ... 31.0 0.0 Excel says: #VALUE! 112 !FE ... 31.0 4.0 Excel says: #REF!

##### HLOOKUP (equality)
 A B C D E F G 114 one =HLOOKUP( C114, D\$114:G\$116, 1.0, false ) one one two three four 115 три =HLOOKUP( C115, D\$114:G\$116, 2.0, false ) three один 116 vier =HLOOKUP( C116, D\$114:G\$116, 3.0, false ) four eins 117 one =HLOOKUP( C117, D\$114:G\$116, D117, false ) one 1.0 118 три ... three 2.0 119 vier ... four 3.0 120 !NA ... five 1.0 Excel says: #N/A 121 !FE ... one 0.0 Excel says: #VALUE! 122 !FE ... one 4.0 Excel says: #REF!

##### VLOOKUP
 A B C D E F 124 22.0 =VLOOKUP( C124, D\$124:F\$126, 3.0 ) 29.0 10.0 11.0 21.0 125 23.0 =VLOOKUP( C125, D\$124:F\$126, 3.0 ) 30.0 20.0 126 23.0 =VLOOKUP( C126, D\$124:F\$126, 3.0 ) 31.0 30.0 127 20.0 =VLOOKUP( C127, D\$124:F\$126, D127 ) 29.0 1.0 128 13.0 ... 30.0 2.0 129 23.0 ... 31.0 3.0 130 !FE ... 31.0 0.0 Excel says: #VALUE! 131 !FE ... 31.0 4.0 Excel says: #REF!

##### VLOOKUP (equality)
 A B C D E F 133 one =VLOOKUP( C133, D\$133:F\$135, 1.0, false ) one one один eins 134 два =VLOOKUP( C134, D\$133:F\$135, 2.0, false ) two two 135 drei =VLOOKUP( C135, D\$133:F\$135, 3.0, false ) three three 136 one =VLOOKUP( C136, D\$133:F\$135, D136, false ) one 1.0 137 два ... two 2.0 138 drei ... three 3.0 139 !NA ... four 1.0 Excel says: #N/A 140 !FE ... one 0.0 Excel says: #VALUE! 141 !FE ... one 4.0 Excel says: #REF!

##### CHOOSE
 A B C D E F 143 11.0 =CHOOSE( C143, D143, E143, F143 ) 1.0 11.0 12.0 13.0 144 22.0 ... 2.0 21.0 22.0 23.0 145 33.0 ... 3.0 31.0 32.0 33.0 146 !FE ... 4.0 11.0 12.0 13.0 Excel says: #VALUE! 147 !FE ... 0.0 11.0 12.0 13.0 Excel says: #VALUE! 148 12.0 ... 2.99 11.0 12.0 13.0