Formula for multiple conditions?

[rraymond]

I need help creating a report based on a fluid raw data file. Earlier, in another post, I got help and managed to generate this formula:

=IF(ISNA(MATCH("NorthwestAcuraAirport Acura",'April 2011 Raw Data'!$A:$A,0)),0,INDEX('April 2011 Raw Data'!I:I,MATCH("NorthwestAcuraAirport Acura",'April 2011 Raw Data'!$A:$A,0)))

While this formula is wonderful, I still need to update the 'NorthwestAcuraAirport Acura" portion hundreds of times based upon Zone/Make/Dealer, and what happens if the order of the dealers change? Then I have to modify the formulas each time.

A better solution would be a similar formula that searches for the condition of where Zone (Northwest) Make (Acura) and Dealer are present, based upon a cell location - or something like that. I know this is vague, but can anyone help? I'm using Excel 2003.

 

[Jonmo1]

You can replace "NorthwesternAcuraAirport Acura" with a cell reference containing the same..

=IF(ISNA(MATCH=(A1,'April 2011 Raw Data'!$A:$A,0)),0,INDEX('April 2011 Raw Data'!I:I,MATCH(A1,'April 2011 Raw Data'!$A:$A,0)))

A1 = NorthwesternAcuraAirport Acura

Now if your needs change, you just have to change the value in A1, not the formula.

 

[rraymond]

Jonmo, you're good, you understand what I'm getting at, but I'm being a little unclear. The constant is the dealer name, so there's that cell reference that works fine, but the zones and dealer names change. So... I would need a formula that looks for the value in your A1 only if Northwest and Acura were present. If not, then return a zero value. This way I can embed formulas in the NW column that just search for NW data, SW column, etc.

 

[rraymond]

Okay, really struggling with this here. I came up with this formula, which in a previous config gave me a 0 answer, which meant I was doing something right. But now all I'm getting is a general error message about too many arguments or missing a parenthesis or something.

=IF(ISNA(MATCH(B2,'April 2011 Raw Data'!$A:$A,0)),0,INDEX('April 2011 Raw Data'!I:I,MATCH(B2,'April 2011 Raw Data'!$A:$A,0),IF(AND('April 2011 Raw Data'!$C:$C="Northwest",'April 2011 Raw Data'!$D:$D="Acura",0))))

B2 refers to my dealer name, which can vary in that cell location every month. What I need is a formula that searches for the value in B2 and only returns it if "Northwest" and "Acura" are also present in the same row.

Please help.