+ Reply to Thread
Results 1 to 5 of 5

How do I search excel spreadsheets using multiple search criteria.

  1. #1
    Kasper
    Guest

    How do I search excel spreadsheets using multiple search criteria.

    I am trying to extract data from a spreadsheet using a search function that
    has two search criteria. For example, I have a worksheet filled with
    employee performance data and need to extract data to a table based employee
    name AND date. Something similar to VLOOKUP but with two filters instead of
    one.

  2. #2
    Dave Peterson
    Guest

    Re: How do I search excel spreadsheets using multiple search criteria.

    You can use this kind of syntax:

    =index(othersheet!$c$1:$c$100,
    match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
    (one cell)

    This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
    correctly, excel will wrap curly brackets {} around your formula. (don't type
    them yourself.)

    Adjust the range to match--but you can't use the whole column.

    Kasper wrote:
    >
    > I am trying to extract data from a spreadsheet using a search function that
    > has two search criteria. For example, I have a worksheet filled with
    > employee performance data and need to extract data to a table based employee
    > name AND date. Something similar to VLOOKUP but with two filters instead of
    > one.


    --

    Dave Peterson

  3. #3
    Biff
    Guest

    Re: How do I search excel spreadsheets using multiple search criteria.

    Hi!

    One way:

    Entered as an array using the key combo of CTRL,SHIFT,ENTER:

    =INDEX(C1:C10,MATCH(1,(A1:A10="employee_name")*(B1:B10=some_date),0))

    C1:C10 is the range in which the data you want to extract is found.

    Better to use cells to hold the criteria:

    D1 = Bob Jones
    E1 = 12/12/2005

    =INDEX(C1:C10,MATCH(1,(A1:A10=D1)*(B1:B10=E1),0))

    Biff

    "Kasper" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to extract data from a spreadsheet using a search function that
    > has two search criteria. For example, I have a worksheet filled with
    > employee performance data and need to extract data to a table based
    > employee
    > name AND date. Something similar to VLOOKUP but with two filters instead
    > of
    > one.




  4. #4
    Kasper
    Guest

    RE: How do I search excel spreadsheets using multiple search criteria.

    To give a little more info:
    Table is in the range A3:U17 (will gain another row daily)
    Criteria 1 (date) is in column B
    Criteria 2 (agent name) is in column E.

    I need a function that given Agent X on Date Y it will retrieve the data Z
    from the appropriate row.

    "Kasper" wrote:

    > I am trying to extract data from a spreadsheet using a search function that
    > has two search criteria. For example, I have a worksheet filled with
    > employee performance data and need to extract data to a table based employee
    > name AND date. Something similar to VLOOKUP but with two filters instead of
    > one.


  5. #5
    Kasper
    Guest

    RE: How do I search excel spreadsheets using multiple search crite

    Beautiful! Thanks guys!

    "Kasper" wrote:

    > To give a little more info:
    > Table is in the range A3:U17 (will gain another row daily)
    > Criteria 1 (date) is in column B
    > Criteria 2 (agent name) is in column E.
    >
    > I need a function that given Agent X on Date Y it will retrieve the data Z
    > from the appropriate row.
    >
    > "Kasper" wrote:
    >
    > > I am trying to extract data from a spreadsheet using a search function that
    > > has two search criteria. For example, I have a worksheet filled with
    > > employee performance data and need to extract data to a table based employee
    > > name AND date. Something similar to VLOOKUP but with two filters instead of
    > > one.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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