# VLOOKUP and IF Formula to look up duplicate values only once

#### Laura J

##### New Member
Hi All,

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

 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

##### MrExcel MVP, Moderator
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.0012345
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

1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...