Vlookup Help

chisigs2

Board Regular
Joined
Sep 30, 2003
Messages
182
I have gotten this to work in the past..... why wont it work for me now?


[HTML removed by Von Pookie]
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Sep 30, 2003
Messages
182
=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
chisigs2 said:
=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

Board Regular
Joined
Sep 30, 2003
Messages
182

ADVERTISEMENT

I tried it... this is what it returns
302clientbook-1.xls
ABCDE
1CLIENT_NUMCLIENT_NAMEMTD_CollectedPostdates Remaining for the MonthMonth to Date Total
21000FAULKNER UNIV PERKINS REAS0.00#N/A0.00
31007ALICE LLOYD COLL NDSL 034750.00#N/A50.00
41052ALABAMA COMMISSION ESECP400.00#N/A400.00
51089CARSON NEWMAN COLL NDSL0.00FALSE0.00
61094CHRISTIAN BROTHERS INSTITUTIONAL LOAN PR250.00#N/A250.00
71095CHRISTIAN BROTHER PERKINS REAS58.00#N/A58.00
81106CONCORDIA COLLEGE PERKINS REAS813.00#N/A813.00
91117UNIV. OF THE CUMBERLANDS PERKINS PRIM0.00FALSE0.00
101119UNIV. OF THE CUMBERLANDS TUIT PRIM0.00FALSE0.00
111120UNIV. OF THE CUMBERLANDS LOANS PRIM285.00#N/A285.00
121136EASTERN KY UNIV FEE25.00FALSE25.00
131213HERZING COLLEGE TUITION PRIM0.00#N/A0.00
141231JEFFERSON ST JC NDSL REAS69.00#N/A69.00
151243JUDSON COLLEGE PERKINS PRIM ALA120.21#N/A120.21
161263LAMBUTH UNIVERSITY NDSL299.08FALSE299.08
171278LINDENWOOD COLL PERKINS REAS0.00FALSE0.00
181280LINDENWOOD COLLEGE PERKINS PRIM0.00#N/A0.00
191281LEE UNIVERSITY PERKINS REAS4607.92#N/A4607.92
201291UNIV. OF WEST ALABAMA NDSL200.00#N/A200.00
211293UNIV. OF WEST ALABAMA FEE1385.00#N/A1385.00
221294ITT KNOXVILLE PRIM FEE100.00FALSE100.00
231304LOUISVILLE TECH FEE550.00FALSE550.00
241326MOREHOUSE COLLEGE NDSL3763.07#N/A3763.07
251344MEHARRY MED 0501108.87FALSE108.87
261346MEHARRY MED 05030.00#N/A0.00
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
chisigs2 said:
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

Board Regular
Joined
Sep 30, 2003
Messages
182

ADVERTISEMENT

OK... Great....Almost there... and ideas why the 1st 2 arent computing?
302clientbook-1.xls
ABCDE
1CLIENT_NUMCLIENT_NAMEMTD_CollectedPostdates Remaining for the MonthMonth to Date Total
21000FAULKNER UNIV PERKINS REAS0.00#N/A0.00
31007ALICE LLOYD COLL NDSL 034750.00#N/A50.00
41052ALABAMA COMMISSION ESECP400.0093.00493.00
51089CARSON NEWMAN COLL NDSL0.000.000.00
61094CHRISTIAN BROTHERS INSTITUTIONAL LOAN PR250.00250.00500.00
71095CHRISTIAN BROTHER PERKINS REAS58.000.0058.00
81106CONCORDIA COLLEGE PERKINS REAS813.001600.002413.00
91117UNIV. OF THE CUMBERLANDS PERKINS PRIM0.000.000.00
101119UNIV. OF THE CUMBERLANDS TUIT PRIM0.000.000.00
111120UNIV. OF THE CUMBERLANDS LOANS PRIM285.00100.00385.00
121136EASTERN KY UNIV FEE25.000.0025.00
131213HERZING COLLEGE TUITION PRIM0.00255.53255.53
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
chisigs2 said:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,017
Messages
5,569,640
Members
412,284
Latest member
Daibear
Top