Vlookup Help

[chisigs2]

I have gotten this to work in the past..... why wont it work for me now?


[HTML removed by Von Pookie]

 

[Aladin Akyurek]

You have:

=VLOOKUP(A2,'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$C$904,3,FALSE)

which would require that A2 (the lookup value) and the values in postdate302'!$A$1:$A$904 are of the same type: either both text-formatted numbers or both true numbers.

You can ensure them to be of the same type using ASAP Utilities.

BTW, postdate302'!$A$1:$C$904 appears to be sorted in ascending order on column A. If so, you can invoke a faster lookup formula:

=IF(LOOKUP(A2,'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$A$904)=A2,LOOKUP(A2,'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$C$904,0)

Also: Why not

=SUMIF(C2:D2,"<>#N/A")

instead of:

{=SUM(IF(NOT(ISNA(C2:D2)),C2:D2))}

what you have?

If you adopt the lookup formula suggested above, this would become:

=SUM(C2:D2)

 

[chisigs2]

=IFLOOKUP(A2,'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$A$904)=A2,LOOKUP(A2,'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$C$904,0)

I am confused.... can i paste that formula in and it work?

 

[Aladin Akyurek]

=IF(LOOKUP(A2,'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$A$904)=A2,LOOKUP(A2,'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$C$904,0)

I am confused.... can i paste that formula in and it work?

If you take care of the data type problem, yes it would.

Just in order to check:

Does this succeed:

=IF(LOOKUP(A2&"",'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$A$904)=A2,LOOKUP(A2&"",'C:\Client Book\save\[postdate302.xls]postdate302'!$A$1:$C$904),0)

 

[chisigs2]

I tried it... this is what it returns

302clientbook-1.xls
	A	B	C	D	E
1	CLIENT_NUM	CLIENT_NAME	MTD_Collected	Postdates Remaining for the Month	Month to Date Total
2	1000	FAULKNER UNIV PERKINS REAS	0.00	#N/A	0.00
3	1007	ALICE LLOYD COLL NDSL 0347	50.00	#N/A	50.00
4	1052	ALABAMA COMMISSION ESECP	400.00	#N/A	400.00
5	1089	CARSON NEWMAN COLL NDSL	0.00	FALSE	0.00
6	1094	CHRISTIAN BROTHERS INSTITUTIONAL LOAN PR	250.00	#N/A	250.00
7	1095	CHRISTIAN BROTHER PERKINS REAS	58.00	#N/A	58.00
8	1106	CONCORDIA COLLEGE PERKINS REAS	813.00	#N/A	813.00
9	1117	UNIV. OF THE CUMBERLANDS PERKINS PRIM	0.00	FALSE	0.00
10	1119	UNIV. OF THE CUMBERLANDS TUIT PRIM	0.00	FALSE	0.00
11	1120	UNIV. OF THE CUMBERLANDS LOANS PRIM	285.00	#N/A	285.00
12	1136	EASTERN KY UNIV FEE	25.00	FALSE	25.00
13	1213	HERZING COLLEGE TUITION PRIM	0.00	#N/A	0.00
14	1231	JEFFERSON ST JC NDSL REAS	69.00	#N/A	69.00
15	1243	JUDSON COLLEGE PERKINS PRIM ALA	120.21	#N/A	120.21
16	1263	LAMBUTH UNIVERSITY NDSL	299.08	FALSE	299.08
17	1278	LINDENWOOD COLL PERKINS REAS	0.00	FALSE	0.00
18	1280	LINDENWOOD COLLEGE PERKINS PRIM	0.00	#N/A	0.00
19	1281	LEE UNIVERSITY PERKINS REAS	4607.92	#N/A	4607.92
20	1291	UNIV. OF WEST ALABAMA NDSL	200.00	#N/A	200.00
21	1293	UNIV. OF WEST ALABAMA FEE	1385.00	#N/A	1385.00
22	1294	ITT KNOXVILLE PRIM FEE	100.00	FALSE	100.00
23	1304	LOUISVILLE TECH FEE	550.00	FALSE	550.00
24	1326	MOREHOUSE COLLEGE NDSL	3763.07	#N/A	3763.07
25	1344	MEHARRY MED 0501	108.87	FALSE	108.87
26	1346	MEHARRY MED 0503	0.00	#N/A	0.00
Sheet1

 

[Aladin Akyurek]

I tried it... formatted them both as text and it says there is an error.

I had a missing paren... Try the test formula I posted in my previous reply.

 

[chisigs2]

OK... Great....Almost there... and ideas why the 1st 2 arent computing?

302clientbook-1.xls
	A	B	C	D	E
1	CLIENT_NUM	CLIENT_NAME	MTD_Collected	Postdates Remaining for the Month	Month to Date Total
2	1000	FAULKNER UNIV PERKINS REAS	0.00	#N/A	0.00
3	1007	ALICE LLOYD COLL NDSL 0347	50.00	#N/A	50.00
4	1052	ALABAMA COMMISSION ESECP	400.00	93.00	493.00
5	1089	CARSON NEWMAN COLL NDSL	0.00	0.00	0.00
6	1094	CHRISTIAN BROTHERS INSTITUTIONAL LOAN PR	250.00	250.00	500.00
7	1095	CHRISTIAN BROTHER PERKINS REAS	58.00	0.00	58.00
8	1106	CONCORDIA COLLEGE PERKINS REAS	813.00	1600.00	2413.00
9	1117	UNIV. OF THE CUMBERLANDS PERKINS PRIM	0.00	0.00	0.00
10	1119	UNIV. OF THE CUMBERLANDS TUIT PRIM	0.00	0.00	0.00
11	1120	UNIV. OF THE CUMBERLANDS LOANS PRIM	285.00	100.00	385.00
12	1136	EASTERN KY UNIV FEE	25.00	0.00	25.00
13	1213	HERZING COLLEGE TUITION PRIM	0.00	255.53	255.53
Sheet1

 

[Aladin Akyurek]

OK... Great....Almost there... and ideas why the 1st 2 arent computing?

...

=IF(LOOKUP(A2&"",postdate302.xls!$A$1:$A$904)=A2,LOOKUP(A2&"",postdate302.xls!$A$1:$C$904),0)

...

Do 1000 and 1007 exist in the lookup table?

 

[chisigs2]

Do 1000 and 1007 exist in the lookup table?

No they dont... but this is possible for any of the numbers any given month.

 

[Superstar31]

you need "false" at the end?