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. |
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 |
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" wrote in message ... 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. |
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. |
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. |
All times are GMT +1. The time now is 08:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com