Formula requiring three actions

**rraymond** · 05-27-2011, 02:38 PM

I need a formula that requires three things to be present before searching a database and returning a numeric value. I'm running Excel 2003.

**davegugg** · 05-27-2011, 02:48 PM

You can nest up to seven if statements in one formula.

**mewingkitty** · 05-27-2011, 03:03 PM

Yeah sounds like an IF/AND thingy.
=IF(AND(condition 1,condition2,condition3),Result if true,Result if false)
Can't say much else without knowing what the conditions are or how you want the information accessed and how it's organized.

mew!

**MarvinP** · 05-27-2011, 03:04 PM

Hi rraymond and welcome to the forum,

I like Advanced Filters to do this kind of problem. It all depends on what your data looks like and you didn't supply a sample.

Find the attached where I've guessed at what you are looking for.

**rraymond** · 05-27-2011, 03:25 PM

Here's a sample of my data file. Sorry I didn't think of it sooner.

The first tab is my report. The yellow highlights the cells where I want this formula to go. The other two are my data sets.

Things to consider:

1 - The dealer in B2 will change month to month, so it needs to be linked to a different source file with a larger geography (the CSA tab)
2 - The formulas in I2:L5 need to "float" and vary based upon whatever dealer gets popped in to B2.
3 - I was trying to link three variables in which - on the April tab - require columns C, D, and E to be present, link with the value in B2 (which in this case is at F6 on April tab), and then return the value in I6 - 68.

Any help would be great.

**MarvinP** · 05-27-2011, 04:00 PM

Hi,

Find the attached where your data is easily displayed using a Pivot Table. I think you should study Pivots a while and see if they can't do what you are wanting and with much more ease.

**rraymond** · 05-27-2011, 04:13 PM

Marvin, thanks for the help. What I sent was just a detail from a 5-page report, so a pivot table won't work in this case unfortunately. What I can't understand in the file you sent was why the formula generating the "43" works for Northeast, but when I key in Northwest, I get a FALSE. It seems to be taking the first instance of two variables but not matching all three. Do you see what I mean?

**MarvinP** · 05-27-2011, 04:50 PM

I've looked at this for about a half hour and can't figure why it leaves out some column heads. It has to be bad data. Try to convert your data ot values only.

Sometimes where data comes from a mainframe they put "funny" characters in the middle that don't show. I'm wondering if this might be the case with your data.

I'm clueless.