+ Reply to Thread
Results 1 to 7 of 7

Extract values from a database based on a specific criteria

  1. #1
    Registered User
    Join Date
    06-06-2017
    Location
    Paris
    MS-Off Ver
    Excel 2013
    Posts
    9

    Question Extract values from a database based on a specific criteria

    Good morning to you all,

    I am trying to find a way to extract specific information from a database according to a specific criteria.
    Using VLOOKUP or IF functions is not enough, since just part of the list includes the criteria to filter on.
    I can't find a way to not have the lines FALSE or #N/A, except by ordering the column where my text for the criteria used in Excel functions is.

    In the excel attached, tab "invoice list" is the database.
    From this database, I extract various information based on the criteria in column Q.
    For example, tab "b2b" needs to display all information for invoices where "b2b" is mentioned in column Q of tab "invoice list".
    I know how to retrieve data when the invoice number is displayed in B2B tab, but I can't find a way to exclude lines which are not compliant with the criteria in tab B2B.

    Can you help please?
    Attached Files Attached Files

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Extract values from a database based on a specific criteria

    Hi,

    Can you not add an IFERROR function in the beginning?

    Try the following,

    In A5 ('b2b' tab):

    =IFERROR(VLOOKUP(B5,'INVOICE LIST'!C:N,12,FALSE),"")

  3. #3
    Registered User
    Join Date
    06-06-2017
    Location
    Paris
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Extract values from a database based on a specific criteria

    Hello cbatrody,

    Thanks for the quick answer.
    my question is especially on how to fill in column B in tab B2B.
    I'd like to only get invoice numberin column B of tab B2B for invoices where column Q in tab INVOICE LIST is equal to "b2b".
    In my template file, column B is manually populated. I would like the column to be automatically updated based on the values from the database INVOICE LIST.

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Extract values from a database based on a specific criteria

    Hi,

    Try the following array formula in B5 ('b2b' tab):

    =IFERROR(INDEX('INVOICE LIST'!$C$2:$C$13,SMALL(IF('INVOICE LIST'!$Q$2:$Q$13="b2b",ROW('INVOICE LIST'!$C$2:$C$13)-MIN(ROW('INVOICE LIST'!$C$2:$C$13))+1),ROWS($A$1:A1))),"")

    to be confirmed by pressing CTRL+SHIFT+ENTER
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,333

    Re: Extract values from a database based on a specific criteria

    You can try it with Pivot Table
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    06-06-2017
    Location
    Paris
    MS-Off Ver
    Excel 2013
    Posts
    9

    Re: Extract values from a database based on a specific criteria

    Yes! This is exactly what I was trying to do!
    Thanks cbatrody!!

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: Extract values from a database based on a specific criteria

    You are welcome

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

+ 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. Index to Extract Values based on 2 criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-26-2017, 02:49 AM
  2. [SOLVED] Extract values based one criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-10-2016, 10:47 AM
  3. [SOLVED] Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-07-2015, 04:58 PM
  4. [SOLVED] Extract values based on three (3) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-23-2015, 02:30 PM
  5. Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-19-2015, 04:14 AM
  6. [SOLVED] Extract values based on two (2) criteria
    By bjnockle in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-18-2015, 05:10 AM
  7. [SOLVED] Extract duplicated values based on an extra criteria
    By Eduard in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2013, 05:50 AM

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