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

Account numberValue dateTransactionAmountBalance EOD
1234525/07/2018Credit A3.002,058.00
1234531/07/2018Credit B10.002,123.00
1234531/07/2018Credit C25.002,123.00
1234531/07/2018Credit D30.002,123.00
678925/07/2018Credit E5.0092.00
678931/07/2018Credit F5.00122.00
678931/07/2018Credit G10.00122.00
678931/07/2018Credit H15.00122.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
Joined
May 28, 2005
Messages
42,300
Office Version
365
Platform
Windows
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top