+ Reply to Thread
Results 1 to 7 of 7

Match/If/Vlookup to populate a list of data and corresponding values based on a data.

  1. #1
    Registered User
    Join Date
    07-04-2020
    Location
    Dubai
    MS-Off Ver
    Office 2010
    Posts
    30

    Post Match/If/Vlookup to populate a list of data and corresponding values based on a data.

    Hello.
    I work in accounts for a trading company and currently I am preparing a statement of account(Aged receivables) data base manually in excel and when required I filter by a particular customer and send the statement to the client.

    Attached file you will find two worksheets - Sample Database and Sample SOA

    The first one is the database where all information is entered.

    The second sheet (Sample SOA) is where I would like to Auto populate the data. On cell D3 as you can see is where I enter the customer name. (Customer Name needs to be selected from a dropdown list using Data Validation.
    Based on the customer name I would like all the columns Invoice No. /PO number/Department relating to that customer to be auto populated. The data can be populated from Sample Database. It would need to contain all the invoices pertaining to the customer selected.
    In this case. as you can refer the SOA sheet, if I enter or select Customer A, all the list of invoices and the corresponding information such as PO number, Department,Invoice date etc should appear on the statement. Attached file you can see a Sample statement of customer A.

    Please advise how this can be done.

    Note : I do understand it would be simple as to just filter the column in Database sheet by the client and get the information. However I have created the SOA sheet for external purposes so it has a particular format to send to client.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Match/If/Vlookup to populate a list of data and corresponding values based on a data.

    If you don't mind to use helper cells (to reduce load of CPUs).

    Helper cells at.
    J10
    =IFERROR(AGGREGATE(15,6,ROW('Sample Database'!$D$1:$D$31)/('Sample Database'!$D$1:$D$31=$D$3),ROWS(J$9:J9)),0)

    And in the form of report
    A10
    =IF($J10>0,INDEX('Sample Database'!A$1:A$31,$J10),"")
    copy formula to B10, C10

    D10 (copy formula to E10, G10, H10)
    =IF($J10>0,INDEX('Sample Database'!E$1:E$31,$J10),"")

    F10
    =IF($J10>0,IF(INDEX('Sample Database'!G$1:G$31,$J10)<>0,INDEX('Sample Database'!G$1:G$31,$J10),""),"")

    Regards.
    Attached Files Attached Files

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Match/If/Vlookup to populate a list of data and corresponding values based on a data.

    Administrative Note:

    Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    07-04-2020
    Location
    Dubai
    MS-Off Ver
    Office 2010
    Posts
    30

    Re: Match/If/Vlookup to populate a list of data and corresponding values based on a data.

    Thank you so much @menem.
    Much appreciated.

    If you can also provide an option without Helper cells as well? We will keep removing and adding row data on the database so the load would not increase as much.

    Secondly, can you advise how to populate Customer Address on the cell A4 based on the data on cell D3. Am I right to assume we should make another worksheet with the list of client Name and Addresses and use Index match function?
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Match/If/Vlookup to populate a list of data and corresponding values based on a data.

    And your current Excel version is ... ?

  6. #6
    Registered User
    Join Date
    07-04-2020
    Location
    Dubai
    MS-Off Ver
    Office 2010
    Posts
    30

    Re: Match/If/Vlookup to populate a list of data and corresponding values based on a data.

    Hi.

    I am using excel on my work laptop.

    It is Microsoft Office Professional Plus 2016.

  7. #7
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Match/If/Vlookup to populate a list of data and corresponding values based on a data.

    Originally helper columns is use for reduce searching rows of data,
    if your data are unique (base on invoice number) you my move formula in helper cells into column A
    and use index/match from column A to get others data.

    next, formula in helper cells.
    =IFERROR(AGGREGATE(15,6,ROW('Sample Database'!$D$1:$D$31)/('Sample Database'!$D$1:$D$31=$D$3),ROWS(J$9:J9)),0)

    Aggregate will give the row number that met the condition of 'Sample Database'!$D$1:$D$31=$D$3
    please review more on Aggregate function (my english is not good , so I can't explain much)

    Regards.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Populate a cell based of a match - Match/vlookup
    By jambo2013 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2018, 06:55 AM
  2. Populate a cell based of a match - Match/vlookup
    By jambo2013 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2018, 11:04 AM
  3. Replies: 0
    Last Post: 07-09-2014, 02:19 PM
  4. Populate cell data based on data validation (drop down list)
    By ish_baho in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-05-2014, 11:47 PM
  5. [SOLVED] Index Match/Populate data based on key value
    By mzafar in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-03-2013, 01:49 PM
  6. Replies: 2
    Last Post: 05-06-2013, 08:53 AM
  7. Macro to Auto Populate ALL data based on IF/Match Criterias
    By trulyawesome in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-23-2012, 06:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1