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.

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

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" <Kasper@discussions.microsoft.com> wrote in message
news:B9F9E5FD-317B-4DB0-A7EE-762FE43F6AF6@microsoft.com...
>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.

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.

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.