Hi All,
May I please seek your help?
I'm trying to vlookup Balance OED as of 31/07/2018 for each account, sample table below, on a separate sheet. However, multiple lines for each account may have the same Balance EOD and I'd like to look it up only once.
I have tried to use this formula =IF(Sheet1!$B$1:$B$9= 31/7/2018,"VLOOKUP(12345,'Sheet1'!A:E,5,0)","N/A") but without success and I'm not sure how to fix it. The results I'm after are: 2,123.00 for Account 12345 and 122.00 for account 6789
Many thanks for your help
[TABLE="width: 338"]
<colgroup><col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4352;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <tbody>[TR]
[TD="width: 119, bgcolor: transparent"]Account number[/TD]
[TD="width: 80, bgcolor: transparent"]Value date[/TD]
[TD="width: 81, bgcolor: transparent"]Transaction[/TD]
[TD="width: 79, bgcolor: transparent"]Amount[/TD]
[TD="width: 92, bgcolor: transparent"]Balance EOD[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]25/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit A[/TD]
[TD="width: 79, bgcolor: transparent"]3.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,058.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit B[/TD]
[TD="width: 79, bgcolor: transparent"]10.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,123.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit C[/TD]
[TD="width: 79, bgcolor: transparent"]25.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,123.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit D[/TD]
[TD="width: 79, bgcolor: transparent"]30.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,123.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]25/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit E[/TD]
[TD="width: 79, bgcolor: transparent"]5.00[/TD]
[TD="width: 92, bgcolor: transparent"]92.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit F[/TD]
[TD="width: 79, bgcolor: transparent"]5.00[/TD]
[TD="width: 92, bgcolor: transparent"]122.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit G[/TD]
[TD="width: 79, bgcolor: transparent"]10.00[/TD]
[TD="width: 92, bgcolor: transparent"]122.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit H[/TD]
[TD="width: 79, bgcolor: transparent"]15.00[/TD]
[TD="width: 92, bgcolor: transparent"]122.00[/TD]
[/TR]
</tbody>[/TABLE]
May I please seek your help?
I'm trying to vlookup Balance OED as of 31/07/2018 for each account, sample table below, on a separate sheet. However, multiple lines for each account may have the same Balance EOD and I'd like to look it up only once.
I have tried to use this formula =IF(Sheet1!$B$1:$B$9= 31/7/2018,"VLOOKUP(12345,'Sheet1'!A:E,5,0)","N/A") but without success and I'm not sure how to fix it. The results I'm after are: 2,123.00 for Account 12345 and 122.00 for account 6789
Many thanks for your help
[TABLE="width: 338"]
<colgroup><col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4352;"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <tbody>[TR]
[TD="width: 119, bgcolor: transparent"]Account number[/TD]
[TD="width: 80, bgcolor: transparent"]Value date[/TD]
[TD="width: 81, bgcolor: transparent"]Transaction[/TD]
[TD="width: 79, bgcolor: transparent"]Amount[/TD]
[TD="width: 92, bgcolor: transparent"]Balance EOD[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]25/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit A[/TD]
[TD="width: 79, bgcolor: transparent"]3.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,058.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit B[/TD]
[TD="width: 79, bgcolor: transparent"]10.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,123.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit C[/TD]
[TD="width: 79, bgcolor: transparent"]25.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,123.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]12345[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit D[/TD]
[TD="width: 79, bgcolor: transparent"]30.00[/TD]
[TD="width: 92, bgcolor: transparent"]2,123.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]25/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit E[/TD]
[TD="width: 79, bgcolor: transparent"]5.00[/TD]
[TD="width: 92, bgcolor: transparent"]92.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit F[/TD]
[TD="width: 79, bgcolor: transparent"]5.00[/TD]
[TD="width: 92, bgcolor: transparent"]122.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit G[/TD]
[TD="width: 79, bgcolor: transparent"]10.00[/TD]
[TD="width: 92, bgcolor: transparent"]122.00[/TD]
[/TR]
[TR]
[TD="width: 119, bgcolor: transparent"]6789[/TD]
[TD="width: 80, bgcolor: transparent"]31/07/2018[/TD]
[TD="width: 81, bgcolor: transparent"]Credit H[/TD]
[TD="width: 79, bgcolor: transparent"]15.00[/TD]
[TD="width: 92, bgcolor: transparent"]122.00[/TD]
[/TR]
</tbody>[/TABLE]