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
102.0...25.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
211.0...25.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
32!NA...25.010.020.030.0Excel says: #N/A

INDEX (1-dim)
ABCDEF
376.0=INDEX(C37:E37,2.0)5.06.07.0
389.0...8.09.010.0
376.0...5.06.07.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...8.09.010.0
499.0...5.06.07.0
5211.0=INDEX(F52:G53,C52,D52)1.01.0
5313.0=INDEX(F52:G53,C53,D53)1.02.0
5412.0=INDEX(F52:G53,C54,D54)2.01.0
5514.0...2.02.0
5711.0=INDEX(C57:D58,1.0,1.0)11.013.0
5812.0...12.014.0
5711.0...11.013.0
6011.0=INDEX(F60:G61,C60,D60)1.01.011.013.0
6114.0...1.02.012.014.0
6011.0...1.01.011.013.0
64!FE=INDEX(E66:F67,C64,1.0)-1.0Excel says: #VALUE!
65!FE...0.0Excel says: #VALUE!
6611.0=INDEX(E66:F67,C66,1.0)1.0
6712.0=INDEX(E66:F67,C67,1.0)2.0
68!FE=INDEX(E66:F67,C68,1.0)3.0Excel says: #REF!

INDEX (return string)
ABCDE
70one=INDEX(D70:E70,C70)1.0onetwo
71two...2.0onetwo

LOOKUP (vectors horiz.)
ABCDEFGHI
7312.0=LOOKUP(C73,D73:F73,G73:I73)20.010.020.030.011.012.013.07.0
7411.0...19.010.020.030.011.012.013.07.0
7512.0...21.010.020.030.011.012.013.07.0
76!NA...9.010.020.030.011.012.013.07.0Excel says: #N/A
7312.0...20.010.020.030.011.012.013.07.0

LOOKUP (vectors vert.)
ABCDE
7811.0=LOOKUP(C78,D78:D80,E78:E80)19.010.011.0
7912.0=LOOKUP(C79,D78:D80,E78:E80)20.020.0
8012.0=LOOKUP(C80,D78:D80,E78:E80)21.030.0
81!NA=LOOKUP(C81,D78:D80,E78:E80)9.0Excel says: #N/A

LOOKUP (array horiz)
ABCDEFG
8322.0=LOOKUP(C83,D83:G85)29.010.020.030.040.0
8423.0=LOOKUP(C84,D83:G85)30.011.0
8523.0=LOOKUP(C85,D83:G85)31.021.0

LOOKUP (array square)
ABCDEF
8721.0=LOOKUP(C87,D87:F89)19.010.011.021.0
8822.0=LOOKUP(C88,D87:F89)20.020.0
8922.0=LOOKUP(C89,D87:F89)21.030.0

LOOKUP (array vert)
ABCDEF
9121.0=LOOKUP(C91,D91:F94)19.010.011.021.0
9222.0=LOOKUP(C92,D91:F94)20.020.0
9322.0...21.030.0
9423.0=LOOKUP(C94,D91:F94)30.040.0

HLOOKUP
ABCDEFG
9622.0=HLOOKUP(C96,D96:G98,3.0)29.010.020.030.040.0
9723.0=HLOOKUP(C97,D96:G98,3.0)30.011.0
9823.0=HLOOKUP(C98,D96:G98,3.0)31.021.0
9920.0=HLOOKUP(C99,D96:G98,D99)29.01.0
10013.0...30.02.0
10123.0...31.03.0
102!FE...31.00.0Excel says: #VALUE!
103!FE...31.04.0Excel says: #REF!

VLOOKUP
ABCDEF
10522.0=VLOOKUP(C105,D105:F107,3.0)29.010.011.021.0
10623.0=VLOOKUP(C106,D105:F107,3.0)30.020.0
10723.0=VLOOKUP(C107,D105:F107,3.0)31.030.0
10820.0=VLOOKUP(C108,D105:F107,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!

CHOOSE
ABCDEF
11411.0=CHOOSE(C114,D114,E114,F114)1.011.012.013.0
11522.0...2.021.022.023.0
11633.0...3.031.032.033.0
117!FE...4.011.012.013.0Excel says: #VALUE!
118!FE...0.011.012.013.0Excel says: #VALUE!
11912.0...2.9911.012.013.0