Excel 2007 : Formula for multiple criteria

I am having issues creating a formula that will give me a set value based on multiple criteria.
Example:
If Col A="yes"
AND if Col B="No"
AND if Col C= "2"
AND if Col D= "B"
AND if Col E= "X"
Then "Col F"
if not "0"

I am putting together a multiple sheet workbook that is used as a template to submit to the Medicare. I am currently having to manually look up and enter all information for each discipline. What I want is for a cell to look on another worksheet search data in 5 or 6 columns and if they all match the specific criteria asked for it returns the value listed.

The data worksheet contains about 1500 lines and 30 columns total...I only need to use a specific few columns, but every line.

We have tried mutiple/nested if statements and and statements (although not sure I am doing them right) and always get an error about too many criteria.

Any help would be appreciated, thanks!

Ok I tried that formula and am getting a #Value error, which is better than nothing, but isn't helping me.

Let me try to better explain. My "Data" is 1500 lines of names, titles, IDs, numbers, dates, figures, dollars, etc. I need to search all 1500 lines and 5 or 6 columns and return a value if all criteria in the same line are true. I created a sample workbook since I can't send my actual data. I am needing the formula to search all lines of data to return the corresponding number to the letter sequence I am searching for. I also left the formula on there with the Value error....maybe I entered something wrong.

Hopefully the attachment works.

Data page is the data that needs to be searched and my results page is what I am searching for.

Thanks again for the help!

Hi Mandy,

Not really quite sure want you are after, but check out the attached.

If I read it right it almost seems like you require a Vlookup.

Look on the Data tab and I turned it into a table based off the comments you had.

Rocky1

I had thought that a LOOKUP formula would work but have NEVER used one. I don't know how to read it but I don't think thats coming out right. I attached more specific data for what I am needing.

I need a formula that will search data and return a specific value, both number and dollar amount, On the attachment I have listed data that resembles what I am needing to search. The results page has the configurations I need it to search and return a value and dollar amount for.

I have reattached the excel test.

I know this is confusing, we worked on it for a few hours the other day with no luck.

There may be no way for us to enter a formula....right now I am manually looking up data and manually transferring information from one worksheet to another. Its easy work but when you have hundreds of these to do, time adds up. We would like for it to be all automatic as soon as the data is downloaded into the template.

Thanks again everyone!

ConneXionLost

I cant seperate the results sheet and the data is already seperated so I don't think that will work.

Martindwilson

That worked great for the ABCDE example but when I copied and modified it for the 933 W OUT example I am just getting "0" as a result for everything. I might be modifying it wrong but would you mind looking at? I really appreciate it!!

I have attached the excel workbook with the iferror formula so you could see if I modified it wrong. Thanks again!!

Thank you Thank you Thank you.....I tested it with my actual formula and it works perfect....

Thanks again!!

Hi,

Would need your expertise on this.

I'm trying to capture the highest number on a given group. Been playing around with index and match and I can't seem to make it worked.

For example,

Group Name, count
group A, 12
group A, 13
group A, 15
group B, 3
group B, 2

the output cell should give me something like:
Group Name, max count
group A, 15
group B, 3

thanks guys...