Vlookup value error

legalhustler · Jun 18, 2015

I have a text string in column A and a 6 digit value in column B. I am trying to return the values in green.

..........................................Column A............................................................................Column B.........

201506 075 2473000610000000000000001298705828DE F017 ................................................610000
201506 075 2480000298500000000000002705814212DB F017 A .............................................298500

I have the following formula but it returns #Value error.

VLOOKUP(MID(A2,22,4),MID($A$2:$B$2,22,4),2,0)

This part of the formula MID(A2,22,4) is looking at the values in red.

Can someone help. And No I simply cannot do a mid of column A and get the 6 digit value b/c column B can have different values.

legalhustler · Jun 19, 2015

To further simply this let's say my data is laid out like this (we can forget the sample data for now):

Column A (GLs).............Column B (Funds)
101000.................................2473
101000.................................2473
101000.................................2450
610000.................................2473
610000.................................2473
298500.................................2480
298500.................................2475
298500.................................2475
No Match...............................2475

What I expect is list of unique GLs AND unique Funds using a formula [Note: both criteria's has to be met....use Boolean multiplication?]. I know I can do Advanced Query if I highlight both column and it'll extract what I want. So the final output would look like this:

101000..........2473
101000..........2450
610000..........2473
298500..........2480
298500..........2475
No Match........2475

I would like a formula that would give me results like the above. Thanks.

Aladin Akyurek · Jun 21, 2015

legalhustler said:
Mr. Aladin,

The lookup table is the entire array of values in column A and the lookup value is single value (like 2473) within column A.

I personally resolved my issue of extracting unique GL's and summing their amount (see columns D & E) in the link below. My next goal is to extract GL's by fund (See Expected Resulted in columns G & H). I think it's a matter of tweaking my formula a little. I would like to do it without a helper column if possible, but it's okay if that can't be done.

https://app.box.com/s/q37zr5emh2n0fdcev7k0z9qf9p6iypfv

Thanks!

Given this input:

Row\Col	A
1	Text File
2	201506 075 2473000101000000000000002196975429CB G0990000
3	201506 075 2473000101000000000000002196975429DB N
4	201506 075 2473000101000000000000002196975429CE G0990000
5	201506 075 2473000101000000000000002196975429DE N
6	201506 075 2450000102400000000000000029232203CE G0990000
7	201506 075 2473000610000000000000005654931203DE N
8	201506 075 2473000610000000000000001298705828DE F017
9	201506 075 2480000298500000000000002705814212DB F017 A
10	201506 075 2475000298500000000000000248806994DB F097 A
11	201506 075 2475000298500000000000001407724339DB G0990000 A
12	201506 075 2475000103500000000000000015000000DB G0990000 A

Are you looking for this output without any so-called helper ranges?

Row\Col	D	E	F
2	GL	Fund	Grand Total
3	101000	2473	$ -
4	102400	2450	$ 292,322.03
5	610000	2473	$ (69,536,370.31)
6	298500	2480	$ (27,058,142.12)
7	298500	2475	$ (16,565,313.33)
8	103500	2475	$ (150,000.00)

Please answer very concisely...

legalhustler · Jun 21, 2015

Aladin Akyurek said:
Given this input:

Row\Col
A
1
Text File
2
201506 075 2473000101000000000000002196975429CB G0990000
3
201506 075 2473000101000000000000002196975429DB N
4
201506 075 2473000101000000000000002196975429CE G0990000
5
201506 075 2473000101000000000000002196975429DE N
6
201506 075 2450000102400000000000000029232203CE G0990000
7
201506 075 2473000610000000000000005654931203DE N
8
201506 075 2473000610000000000000001298705828DE F017
9
201506 075 2480000298500000000000002705814212DB F017 A
10
201506 075 2475000298500000000000000248806994DB F097 A
11
201506 075 2475000298500000000000001407724339DB G0990000 A
12
201506 075 2475000103500000000000000015000000DB G0990000 A

<tbody>
</tbody>

Are you looking for this output without any so-called helper ranges?

Row\Col
D
E
F
2
GL Fund
Grand Total
3
101000 2473
$ -
4
102400 2450
$ 292,322.03
5
610000 2473
$ (69,536,370.31)
6
298500 2480
$ (27,058,142.12)
7
298500 2475
$ (16,565,313.33)
8
103500 2475
$ (150,000.00)

<tbody>
</tbody>

Please answer very concisely...

Thanks for the assistance Aladin.

Just the following two changes needs to be made in your output:

Row 4 GL should say "101000" and row 8 GL should say "No Match" in your results above. This is because 102400 correct GL per the Rules sheet is 101000 (You can see D4 in the Rules sheet shows 102400, but it's correct GL is 101000 in cell E4) and GL 103500 is not found in column D of the Rules sheet. The funds and dollar amounts all looks right correct.

I've tried without helper column but was not successful, thus I created a helper column in my sample table (you can see my formula in D2 of "TB" sheet points to the helper column). This is how I was able to get the unique GL's and it's total dollar amount. Now, the next step is to break it by GL and Fund and get it's total dollar amount.

Let me know if you have any other questions.

Thanks.

Aladin Akyurek · Jun 22, 2015

legalhustler said:
Thanks for the assistance Aladin.

Just the following two changes needs to be made in your output:

Row 4 GL should say "101000" and row 8 GL should say "No Match" in your results above. This is because 102400 correct GL per the Rules sheet is 101000 (You can see D4 in the Rules sheet shows 102400, but it's correct GL is 101000 in cell E4) and GL 103500 is not found in column D of the Rules sheet. The funds and dollar amounts all looks right correct.

[...]

The Rules sheet

Row\Col
B
D
E
2
NAME_L GL (6 digit) SGL (6 digit)
3
DISBURSING AUTHORITY 101000 101000
4
NON 224 COLLECTIONS 102400 101000
5
LIABILITY- NON-ENTITY ASSETS 298500 298500
6
Expense 610000 610000

Row\Col
G
H
I
2
# Characters
Definition
# Loc
3
6
GL ACCOUNT NUMBER
29
4
21
DOLLAR AMOUNT
35
5
1
DEBIT CREDIT INDICATOR
56
6
4
MAIN ACCOUNT CODE
22
7
13
FULL MAIN ACCOUNT CODE + GL ACCOUNT NUMBER
22

01. D3:E6 is named GLXwlk (The definition is yours.)

02. H2 is changed from SGL ACCOUNT NUMBER to GL ACCOUNT NUMBER (Correction).

03. The range in G:I is extended with the last record FULL MAIN ACCOUNT CODE + GL ACCOUNT NUMBER.

04. The relevant cells are in G and I are named as follows:

G2, I2 >> GLLen, GLLoc
G3, I3 >> AmountLen, AmountLoc
G4, I4 >> DCILen, DCILoc
G5, I5 >> MACLen, MACLoc
G6, I6 >> fullMACLen, fullMACLoc

The TB sheet (which houses both the input and the processing/output)

INPUT

Row\Col	A
1	Text File
2	201506 075 2473000101000000000000002196975429CB G0990000
3	201506 075 2473000101000000000000002196975429DB N
4	201506 075 2473000101000000000000002196975429CE G0990000
5	201506 075 2473000101000000000000002196975429DE N
6	201506 075 2450000102400000000000000029232203CE G0990000
7	201506 075 2473000610000000000000005654931203DE N
8	201506 075 2473000610000000000000001298705828DE F017
9	201506 075 2480000298500000000000002705814212DB F017 A
10	201506 075 2475000298500000000000000248806994DB F097 A
11	201506 075 2475000298500000000000001407724339DB G0990000 A
12	201506 075 2475000103500000000000000015000000DB G0990000 A

05. A2:A12 is named VAATB (The definition is yours.)

06. Ivec (from integer vector, see Formulas | Name Manager) is defined as follows:

Rich (BB code):


=ROW(VAATB)-ROW(INDEX(VAATB,1,1))+1

Row\Col	D	E	F	G
1	6
2	GL	SGL	Fund	Grand Total
3	101000	101000	2473	$ -
4	102400	101000	2450	$ 292,322.03
5	610000	610000	2473	$ (69,536,370.31)
6	298500	298500	2480	$ (27,058,142.12)
7	298500	298500	2475	$ (16,565,313.33)
8	103500	No Match	2475	$ (150,000.00)
9
10
11
12

PROCESSING/OUTPUT (D:G)

07. D1, control+shift+enter, not just enter:

Rich (BB code):


=SUM(IF(FREQUENCY(IF(VAATB<>"",MATCH(MID(VAATB,fullMACLoc,fullMACLen),
   MID(VAATB,fullMACLoc,fullMACLen),0)),Ivec),1))

08. D3, control+shift+enter, not just enter, and copy down:

Rich (BB code):


=IF(ROWS($D$3:D3)<=$D$1,INDEX(MID(VAATB,GLLoc,GLLen),
   SMALL(IF(FREQUENCY(IF(VAATB<>"",MATCH(MID(VAATB,fullMACLoc,fullMACLen),
   MID(VAATB,22,13),0)),Ivec),Ivec),ROWS($D$3:D3))),"")

Warning. Do not attempt to eliminate this output range.

Warning. Do not replace ROWS($D$3:D3) with something non-robust like ROW(A1) or ROW(1:1) or less intelligible ROW($1:1).

09. E3, just enter and copy down:

Rich (BB code):


=IF(D3="","",IFERROR(VLOOKUP(D3,GLXwlk,2,0),"No Match"))

10. F3, control+shift+enter, not just enter, and copy down:

Rich (BB code):


=IF(D3="","",INDEX(MID(VAATB,MACLoc,MACLen),
   SMALL(IF(FREQUENCY(IF(VAATB<>"",MATCH(MID(VAATB,fullMACLoc,fullMACLen),
   MID(VAATB,fullMACLoc,fullMACLen),0)),Ivec),Ivec),ROWS($D$3:D3))))

11. G3, control+shift+enter, not just enter, and copy down:

Rich (BB code):


=IF(D3="","",
   SUM(IF(ISNUMBER(SEARCH(F3&"*"&D3,MID(VAATB,fullMACLoc,fullMACLen))),
   (MID($A$2:$A$12,AmountLoc,AmountLen)/100)*
   IF(MID(VAATB,DCILoc,DCILen)="D",-1,1))))

12. See the workbook that implements the foregoing at:

https://dl.dropboxusercontent.com/u/65698317/legalhustler Copy of Sample Data 6.18.15 aa.xlsx

Please do not start another thread on this matter.

legalhustler · Jun 22, 2015

Aladin Akyurek said:
The Rules sheet

Row\Col
B
D
E
2
NAME_L GL (6 digit) SGL (6 digit)
3
DISBURSING AUTHORITY 101000 101000
4
NON 224 COLLECTIONS 102400 101000
5
LIABILITY- NON-ENTITY ASSETS 298500 298500
6
Expense 610000 610000

Row\Col
G
H
I
2
# Characters
Definition
# Loc
3
6
GL ACCOUNT NUMBER
29
4
21
DOLLAR AMOUNT
35
5
1
DEBIT CREDIT INDICATOR
56
6
4
MAIN ACCOUNT CODE
22
7
13
FULL MAIN ACCOUNT CODE + GL ACCOUNT NUMBER
22

01. D3:E6 is named GLXwlk (The definition is yours.)

02. H2 is changed from SGL ACCOUNT NUMBER to GL ACCOUNT NUMBER (Correction).

03. The range in G:I is extended with the last record FULL MAIN ACCOUNT CODE + GL ACCOUNT NUMBER.

04. The relevant cells are in G and I are named as follows:

G2, I2 >> GLLen, GLLoc
G3, I3 >> AmountLen, AmountLoc
G4, I4 >> DCILen, DCILoc
G5, I5 >> MACLen, MACLoc
G6, I6 >> fullMACLen, fullMACLoc

The TB sheet (which houses both the input and the processing/output)

INPUT

Row\Col
A
1
Text File
2
201506 075 2473000101000000000000002196975429CB G0990000
3
201506 075 2473000101000000000000002196975429DB N
4
201506 075 2473000101000000000000002196975429CE G0990000
5
201506 075 2473000101000000000000002196975429DE N
6
201506 075 2450000102400000000000000029232203CE G0990000
7
201506 075 2473000610000000000000005654931203DE N
8
201506 075 2473000610000000000000001298705828DE F017
9
201506 075 2480000298500000000000002705814212DB F017 A
10
201506 075 2475000298500000000000000248806994DB F097 A
11
201506 075 2475000298500000000000001407724339DB G0990000 A
12
201506 075 2475000103500000000000000015000000DB G0990000 A

<tbody>
</tbody>

05. A2:A12 is named VAATB (The definition is yours.)

06. Ivec (from integer vector, see Formulas | Name Manager) is defined as follows:

Rich (BB code):

=ROW(VAATB)-ROW(INDEX(VAATB,1,1))+1

Row\Col
D
E
F
G
1
6
2
GL SGL Fund
Grand Total
3
101000 101000 2473
$ -
4
102400 101000 2450
$ 292,322.03
5
610000 610000 2473
$ (69,536,370.31)
6
298500 298500 2480
$ (27,058,142.12)
7
298500 298500 2475
$ (16,565,313.33)
8
103500 No Match 2475
$ (150,000.00)
9
10
11
12

<tbody>
</tbody>

PROCESSING/OUTPUT (D:G)

07. D1, control+shift+enter, not just enter:

Rich (BB code):

=SUM(IF(FREQUENCY(IF(VAATB<>"",MATCH(MID(VAATB,fullMACLoc,fullMACLen), MID(VAATB,fullMACLoc,fullMACLen),0)),Ivec),1))

08. D3, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($D$3:D3)<=$D$1,INDEX(MID(VAATB,GLLoc,GLLen), SMALL(IF(FREQUENCY(IF(VAATB<>"",MATCH(MID(VAATB,fullMACLoc,fullMACLen), MID(VAATB,22,13),0)),Ivec),Ivec),ROWS($D$3:D3))),"")

Warning. Do not attempt to eliminate this output range.

Warning. Do not replace ROWS($D$3:D3) with something non-robust like ROW(A1) or ROW(1:1) or less intelligible ROW($1:1).

09. E3, just enter and copy down:

Rich (BB code):

=IF(D3="","",IFERROR(VLOOKUP(D3,GLXwlk,2,0),"No Match"))

10. F3, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(D3="","",INDEX(MID(VAATB,MACLoc,MACLen), SMALL(IF(FREQUENCY(IF(VAATB<>"",MATCH(MID(VAATB,fullMACLoc,fullMACLen), MID(VAATB,fullMACLoc,fullMACLen),0)),Ivec),Ivec),ROWS($D$3:D3))))

11. G3, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(D3="","", SUM(IF(ISNUMBER(SEARCH(F3&"*"&D3,MID(VAATB,fullMACLoc,fullMACLen))), (MID($A$2:$A$12,AmountLoc,AmountLen)/100)* IF(MID(VAATB,DCILoc,DCILen)="D",-1,1))))

12. See the workbook that implements the foregoing at:

https://dl.dropboxusercontent.com/u/65698317/legalhustler Copy of Sample Data 6.18.15 aa.xlsx

Please do not start another thread on this matter.

Brilliant! Thank you so much. Few questions:

1) I assume it was necessary to create these named ranges: fullMACLoc,fullMACLen?

2) Anyway I can avoid using the IFERROR function in E3? I want to avoid slow calculation speeds since my data is quite large.

3) Output 08. you hard coded 22,13 I assume that was unintentional

4) Do you think it's wise to keep helper columns, I mean I could combine the formulas so that I eliminate column D, will that slow calculation speeds significantly? My goal is create a drop down list that performs reconciliations by different attributes (GL, Fund etc)

I had to also change the last part of output 11. from -1, 1 to 1,-1

I tried to create my thread based on a small part of a problem that I was trying to figure but sort of unintentionally led to this. I will definitely try to keep related questions on this subject to this thread, if I have any. Thanks again.

Aladin Akyurek · Jun 22, 2015

legalhustler said:
Brilliant! Thank you so much. Few questions:

1) I assume it was necessary to create these named ranges: fullMACLoc,fullMACLen?

No, loc and len names are not per se needed. You can hard-code loc and len values in the formulas if you want to.

2) Anyway I can avoid using the IFERROR function in E3? I want to avoid slow calculation speeds since my data is quite large.

You can replace IFERROR with IFNA if you are on the 2013 version. Not sure though whether IFNA has a better efficiency score. Another option is:

=IF(D3="","",IF(ISNUMBER(MATCH(D3,INDEX(GLXwlk,0,1),0)),VLOOKUP(D3,GLXwlk,2,0),"No Match"))

3) Output 08. you hard coded 22,13 I assume that was unintentional

Unintentional, but see above.

4) Do you think it's wise to keep helper columns, I mean I could combine the formulas so that I eliminate column D, will that slow calculation speeds significantly? My goal is create a drop down list that performs reconciliations by different attributes (GL, Fund etc)

Good grace. In an earlier thread I refused to incorporate the "helper column" formulas you had into the FREQUENCY formula I provided. I had the impression that that refusal (among perhaps other reasons) caused you to start a few threads on this task. Now that I have complied, I get this question! Don't until you have evidence that you suffer slow performance.

I had to also change the last part of output 11. from -1, 1 to 1,-1

You could have pointed out this when you saw post #13 of mine!

I tried to create my thread based on a small part of a problem that I was trying to figure but sort of unintentionally led to this. I will definitely try to keep related questions on this subject to this thread, if I have any.

Ok. I'll keep an eye on you...

Thanks again.

You are welcome.

legalhustler · Jun 23, 2015

No, loc and len names are not per se needed. You can hard-code loc and len values in the formulas if you want to.

It was sagacious of you to create row 7 in the Rules sheet, I would of never thought of it, because it generated the GL's for column D and was used in the foregoing formulas.

You can replace IFERROR with IFNA if you are on the 2013 version. Not sure though whether IFNA has a better efficiency score. Another option is:

=IF(D3="","",IF(ISNUMBER(MATCH(D3,INDEX(GLXwlk,0,1),0)),VLOOKUP(D3,GLXwlk,2,0),"No Match"))

Thanks!

Good grace. In an earlier thread I refused to incorporate the "helper column" formulas you had into the FREQUENCY formula I provided. I had the impression that that refusal (among perhaps other reasons) caused you to start a few threads on this task. Now that I have complied, I get this question! Don't until you have evidence that you suffer slow performance.

No...I was just asking to see which method would be faster. I found a formula timer macro from Charles Williams, MVP...should be an interesting test.

legalhustler · Jun 23, 2015

Aladin Akyurek said:
=IF(D3="","",IF(ISNUMBER(MATCH(D3,INDEX(GLXwlk,0,1),0)),VLOOKUP(D3,GLXwlk,2,0),"No Match"))

This formula doesn't seem to work, it returns 101000 for everything. I tried different ways but couldn't figure it.

legalhustler · Jun 23, 2015

legalhustler said:
This formula doesn't seem to work, it returns 101000 for everything. I tried different ways but couldn't figure it.

Please disregard. I had Calculation Options set to Manual instead of Automatic. Formula works fine.

legalhustler · Jun 26, 2015

I would like to know how to get a unique count from the text strings below. The thing that would make it unique is the values in red needs to be combined with the values in red.

So in the example below there is a unique/distinct count of 5. I tried to create a formula like this but I don't know what to put for the number of characters, thus I left a "?" mark for that part of the argument.

=SUM(IF(FREQUENCY(IF(A1:A7<>"",MATCH(MID(A1:A7,MID(A1,13,8)&MID(A1,29,6),?),MID(A1:A7,MID(A1,13,8)&MID(A1,29,6),?),0)),ROW(A1:A7)-ROW(INDEX(A1:A7,1,1))+1),1))

This is a little different from the previous count Aladin provided. If someone could help I would appreciate it.

TIA!

201506 03620102010 0152000102600000000000000001334914CE F004
201506 03620102010 0152000102600000000000000000952013CE N
201506 03620102010 0152000131100000000000000000342324DB N A
201506 03620102014 0152000131100000000000000000342324DE N A
201506 03620102014 0152000131100000000000000013245515DB N A
201506 03620102010 0152000131300000000000000012149358DE N A
201506 03620102015 0152000131300000000000000000166186DB N A

Vlookup value error

legalhustler

Well-known Member

legalhustler

Well-known Member

Excel Facts

Aladin Akyurek

MrExcel MVP

legalhustler

Well-known Member

Aladin Akyurek

MrExcel MVP

legalhustler

Well-known Member

Aladin Akyurek

MrExcel MVP

legalhustler

Well-known Member

legalhustler

Well-known Member

legalhustler

Well-known Member

legalhustler

Well-known Member

Similar threads

Forum statistics

Share this page

Row\Col	B	D	E
2	NAME_L	GL (6 digit)	SGL (6 digit)
3	DISBURSING AUTHORITY	101000	101000
4	NON 224 COLLECTIONS	102400	101000
5	LIABILITY- NON-ENTITY ASSETS	298500	298500
6	Expense	610000	610000

Row\Col	G	H	I
2	# Characters	Definition	# Loc
3	6	GL ACCOUNT NUMBER	29
4	21	DOLLAR AMOUNT	35
5	1	DEBIT CREDIT INDICATOR	56
6	4	MAIN ACCOUNT CODE	22
7	13	FULL MAIN ACCOUNT CODE + GL ACCOUNT NUMBER	22

Vlookup value error

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock