VLOOKUP and IF Formula to look up duplicate values only once

Laura J

New Member
Joined
Aug 31, 2018
Messages
6
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]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
If you have the MAXIFS function available, then try the I2 formula copied down. Otherwise try the J2 formula.

Excel Workbook
ABCDEFGHIJ
1Account numberValue dateTransactionAmountBalance EODA/c Num31/07/2018
21234525/07/2018Credit A32,058.001234521232123
31234531/07/2018Credit B102,123.006789122122
41234531/07/2018Credit C252,123.00
51234531/07/2018Credit D302,123.00
6678925/07/2018Credit E592
7678931/07/2018Credit F5122
8678931/07/2018Credit G10122
9678931/07/2018Credit H15122
Lookup Value
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top