VLOOKUP and IF Formula to look up duplicate values only once

[Laura J]

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]

 

[Peter_SSs]

If you have the MAXIFS function available, then try the I2 formula copied down. Otherwise try the J2 formula.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Account number	Value date	Transaction	Amount	Balance EOD			A/c Num	31/07/2018
2	12345	25/07/2018	Credit A	3	2,058.00			12345	2123	2123
3	12345	31/07/2018	Credit B	10	2,123.00			6789	122	122
4	12345	31/07/2018	Credit C	25	2,123.00
5	12345	31/07/2018	Credit D	30	2,123.00
6	6789	25/07/2018	Credit E	5	92
7	6789	31/07/2018	Credit F	5	122
8	6789	31/07/2018	Credit G	10	122
9	6789	31/07/2018	Credit H	15	122
Lookup Value