Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Retrieving data from a database list
Hi,
I would like to know how to retrieve one value from a database list based on multiple criteria. My list as the following headings: Company, Year, Revenue ($) I would like to perform some form of lookup function to retrieve a unique value based on several criteria. E.g. If Company = "company 1" and Year = "2000", return the value for "Revenue ($)". Bear in mind that there are several hundred company names, and that I would like to be able to copy this formula into other cells, referencing different criteria. I've tried DGET, but can't work out how to use. Any suggestions would be much appreciated. RA |
#2
|
|||
|
|||
Hi
=SUMPRODUCT(--(Company="company 1"),--(Year=2001),Revenue) Where Company, Year and Revenue are references to data ranges in according columns (NB! All ranges must be of same dimension). Arvi Laanemets "RestlessAde" wrote in message ... Hi, I would like to know how to retrieve one value from a database list based on multiple criteria. My list as the following headings: Company, Year, Revenue ($) I would like to perform some form of lookup function to retrieve a unique value based on several criteria. E.g. If Company = "company 1" and Year = "2000", return the value for "Revenue ($)". Bear in mind that there are several hundred company names, and that I would like to be able to copy this formula into other cells, referencing different criteria. I've tried DGET, but can't work out how to use. Any suggestions would be much appreciated. RA |
#3
|
|||
|
|||
Thanks Arvi, that works really great. (I would never have thought to use
SumProduct - bizzare!) RA "Arvi Laanemets" wrote: Hi =SUMPRODUCT(--(Company="company 1"),--(Year=2001),Revenue) Where Company, Year and Revenue are references to data ranges in according columns (NB! All ranges must be of same dimension). Arvi Laanemets "RestlessAde" wrote in message ... Hi, I would like to know how to retrieve one value from a database list based on multiple criteria. My list as the following headings: Company, Year, Revenue ($) I would like to perform some form of lookup function to retrieve a unique value based on several criteria. E.g. If Company = "company 1" and Year = "2000", return the value for "Revenue ($)". Bear in mind that there are several hundred company names, and that I would like to be able to copy this formula into other cells, referencing different criteria. I've tried DGET, but can't work out how to use. Any suggestions would be much appreciated. RA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a list in excel that contains external data? | Excel Discussion (Misc queries) | |||
Auto scroll down data validation list | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions | |||
How do I create a "List If" function.I need to search a database . | Excel Worksheet Functions |