# Vlookup Help

#### chisigs2

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

[HTML removed by Von Pookie]

### Excel Facts

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

##### MrExcel MVP
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")

{=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
=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?

##### MrExcel MVP
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

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

##### MrExcel MVP
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

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

##### MrExcel MVP
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?

#### chisigs2

##### Board Regular
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

##### Active Member
you need "false" at the end?

Replies
3
Views
53
Replies
2
Views
31
Replies
0
Views
56
Replies
1
Views
121
Replies
14
Views
208