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

Account number	Value date	Transaction	Amount	Balance EOD
12345	25/07/2018	Credit A	3.00	2,058.00
12345	31/07/2018	Credit B	10.00	2,123.00
12345	31/07/2018	Credit C	25.00	2,123.00
12345	31/07/2018	Credit D	30.00	2,123.00
6789	25/07/2018	Credit E	5.00	92.00
6789	31/07/2018	Credit F	5.00	122.00
6789	31/07/2018	Credit G	10.00	122.00
6789	31/07/2018	Credit H	15.00	122.00

<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> </tbody>

 

[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