AFC - Abacus Formula Compiler for Java

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)
ABCDEF
2!NA=MATCH( C2, D2:F2, 1.0 )99.0100.0200.0500.0Excel says: #N/A
31.0...100.0100.0200.0500.0
41.0...101.0100.0200.0500.0
51.0...199.0100.0200.0500.0
62.0...200.0100.0200.0500.0
72.0...201.0100.0200.0500.0
83.0...500.0100.0200.0500.0
93.0...501.0100.0200.0500.0
102.0=MATCH( C10, D10:F10 )25.010.020.030.0
111.0...15.010.020.030.0

MATCH (descending)
ABCDEF
133.0=MATCH( C13, D13:F13, (-1.0) )99.0500.0200.0100.0
143.0...100.0500.0200.0100.0
152.0...101.0500.0200.0100.0
162.0...199.0500.0200.0100.0
172.0...200.0500.0200.0100.0
181.0...201.0500.0200.0100.0
191.0...500.0500.0200.0100.0
20!NA...501.0500.0200.0100.0Excel says: #N/A
211.0...25.030.020.010.0
222.0...15.030.020.010.0

MATCH (equality)
ABCDEF
24!NA=MATCH( C24, D24:F24, 0.0 )99.0100.0200.0500.0Excel says: #N/A
251.0...100.0100.0200.0500.0
26!NA...101.0100.0200.0500.0Excel says: #N/A
27!NA...199.0100.0200.0500.0Excel says: #N/A
282.0...200.0100.0200.0500.0
29!NA...201.0100.0200.0500.0Excel says: #N/A
303.0...500.0100.0200.0500.0
31!NA...501.0100.0200.0500.0Excel says: #N/A
32!NA...25.010.020.030.0Excel says: #N/A
332.0...20.010.020.030.0
342.0...20.030.020.010.0

INDEX (1-dim)
ABCDEF
376.0=INDEX( C37:E37, 2.0 )5.06.07.0
389.0...8.09.010.0
405.0=INDEX( D40:F40, C40 )2.04.05.06.0
418.0...3.06.07.08.0
426.0...1.06.07.08.0
43!FE...0.06.07.08.0Excel says: #VALUE!
44!FE...4.06.07.08.0Excel says: #REF!
460.0...2.0
475.0...2.05.0

INDEX (2-dim)
ABCDEFG
499.0=INDEX( C49:E50, 2.0, 2.0 )5.06.07.0
509.0=INDEX( C50:E50, 1.0, 2.0 )8.09.010.0
5211.0=INDEX( F$52:G$53, C52, D52 )1.01.0
5313.0=INDEX( F$52:G$53, C53, D53 )1.02.0
5412.0=INDEX( F$52:G$53, C54, D54 )2.01.0
5514.0...2.02.0
5711.0=INDEX( C57:D58, 1.0, 1.0 )11.013.0
5812.0=INDEX( C58:D58, 1.0, 1.0 )12.014.0
6011.0=INDEX( F$60:G$61, C60, D60 )1.01.011.013.0
6114.0=INDEX( F$61:G$61, C61, D61 )1.02.012.014.0

INDEX (1-dim, 2-idx)
ABCD
63!FE=INDEX( E63:G63, D63, C63 )2.0-1.010.0Excel says: #VALUE!
6411.0=INDEX( E64:G64, 0.0, C64 )2.0
6511.0=INDEX( E65:G65, D65, C65 )2.01.010.0
66!FE...2.02.010.0Excel says: #REF!
68!FE=INDEX( E$68:E$70, C68, D68 )2.0-1.010.0Excel says: #VALUE!
6911.0=INDEX( E$68:E$70, C69, 0.0 )2.0
7011.0=INDEX( E$68:E$70, C70, D70 )2.01.012.0
71!FE=INDEX( E$68:E$70, C71, D71 )2.02.0Excel says: #REF!
73!FE=INDEX( E$75:F$76, C73, 1.0 )-1.0Excel says: #VALUE!
74!FE...0.0Excel says: #VALUE!
7511.0=INDEX( E$75:F$76, C75, 1.0 )1.0
7612.0=INDEX( E$75:F$76, C76, 1.0 )2.0
77!FE=INDEX( E$75:F$76, C77, 1.0 )3.0Excel says: #REF!

INDEX (return string)
ABCDE
79one=INDEX( D79:E79, C79 )1.0onetwo
80two...2.0onetwo

LOOKUP (vectors horiz.)
ABCDEFGHI
8212.0=LOOKUP( C82, D82:F82, G82:I82 )20.010.020.030.011.012.013.07.0
8311.0...19.010.020.030.011.012.013.07.0
8412.0...21.010.020.030.011.012.013.07.0
85!NA...9.010.020.030.011.012.013.07.0Excel says: #N/A

LOOKUP (vectors vert.)
ABCDE
8711.0=LOOKUP( C87, D$87:D$89, E$87:E$89 )19.010.011.0
8812.0=LOOKUP( C88, D$87:D$89, E$87:E$89 )20.020.0
8912.0=LOOKUP( C89, D$87:D$89, E$87:E$89 )21.030.0
90!NA=LOOKUP( C90, D$87:D$89, E$87:E$89 )9.0Excel says: #N/A

LOOKUP (array horiz)
ABCDEFG
9222.0=LOOKUP( C92, D$92:G$94 )29.010.020.030.040.0
9323.0=LOOKUP( C93, D$92:G$94 )30.011.0
9423.0=LOOKUP( C94, D$92:G$94 )31.021.0

LOOKUP (array square)
ABCDEF
9621.0=LOOKUP( C96, D$96:F$98 )19.010.011.021.0
9722.0=LOOKUP( C97, D$96:F$98 )20.020.0
9822.0=LOOKUP( C98, D$96:F$98 )21.030.0

LOOKUP (array vert)
ABCDEF
10021.0=LOOKUP( C100, D$100:F$103 )19.010.011.021.0
10122.0=LOOKUP( C101, D$100:F$103 )20.020.0
10222.0...21.030.0
10323.0=LOOKUP( C103, D$100:F$103 )30.040.0

HLOOKUP
ABCDEFG
10522.0=HLOOKUP( C105, D$105:G$107, 3.0 )29.010.020.030.040.0
10623.0=HLOOKUP( C106, D$105:G$107, 3.0 )30.011.0
10723.0=HLOOKUP( C107, D$105:G$107, 3.0 )31.021.0
10820.0=HLOOKUP( C108, D$105:G$107, D108 )29.01.0
10913.0...30.02.0
11023.0...31.03.0
111!FE...31.00.0Excel says: #VALUE!
112!FE...31.04.0Excel says: #REF!

HLOOKUP (equality)
ABCDEFG
114one=HLOOKUP( C114, D$114:G$116, 1.0, false )oneonetwothreefour
115три=HLOOKUP( C115, D$114:G$116, 2.0, false )threeодин
116vier=HLOOKUP( C116, D$114:G$116, 3.0, false )foureins
117one=HLOOKUP( C117, D$114:G$116, D117, false )one1.0
118три...three2.0
119vier...four3.0
120!NA...five1.0Excel says: #N/A
121!FE...one0.0Excel says: #VALUE!
122!FE...one4.0Excel says: #REF!

VLOOKUP
ABCDEF
12422.0=VLOOKUP( C124, D$124:F$126, 3.0 )29.010.011.021.0
12523.0=VLOOKUP( C125, D$124:F$126, 3.0 )30.020.0
12623.0=VLOOKUP( C126, D$124:F$126, 3.0 )31.030.0
12720.0=VLOOKUP( C127, D$124:F$126, D127 )29.01.0
12813.0...30.02.0
12923.0...31.03.0
130!FE...31.00.0Excel says: #VALUE!
131!FE...31.04.0Excel says: #REF!

VLOOKUP (equality)
ABCDEF
133one=VLOOKUP( C133, D$133:F$135, 1.0, false )oneoneодинeins
134два=VLOOKUP( C134, D$133:F$135, 2.0, false )twotwo
135drei=VLOOKUP( C135, D$133:F$135, 3.0, false )threethree
136one=VLOOKUP( C136, D$133:F$135, D136, false )one1.0
137два...two2.0
138drei...three3.0
139!NA...four1.0Excel says: #N/A
140!FE...one0.0Excel says: #VALUE!
141!FE...one4.0Excel says: #REF!

CHOOSE
ABCDEF
14311.0=CHOOSE( C143, D143, E143, F143 )1.011.012.013.0
14422.0...2.021.022.023.0
14533.0...3.031.032.033.0
146!FE...4.011.012.013.0Excel says: #VALUE!
147!FE...0.011.012.013.0Excel says: #VALUE!
14812.0...2.9911.012.013.0