Thread: Match function nested in an If statement

Hi dforte,

Try this:

Code:

=IF(AND(NOT(ISNA(MATCH(B5,'FY 06'!$B$5:$B$24,0))),K5=1,'FY 06'!K5=1),0,1)

Cheers,

Hi dforte,

Okay, this should meet the requirement for your second post:

Code:

=IF(AND(NOT(ISNA(MATCH(B5,'FY 06'!$B$5:$B$24,0))),'FY 06'!K5=1),0,1)

But note in your first post where you said, "and has a "1" in both columns K of each sheet". This appears to be contradictory.

Cheers,

Hi dforte,

Ah! Sorry, my bad for not seeing that part! How about this one:

Code:

=IF(AND(NOT(ISNA(MATCH(B5,'FY 06'!$B$5:$B$24,0))),K5=1,'FY 06'!K5=1),0,IF(ISNA(MATCH(B5,'FY 06'!$B$5:$B$24,0)),0,1))

Cheers,

just realized the problem... We're assuming that the two K cells correspond to the same person, however, it is possible between sheets that there are more names offsetting the place of the same two names...

Is there a way to make the "'FY 06'!K5=1" within the AND statement correspond to the name that it is looking for within the MATCH statement?

Hi dforte,

Again, I'm confused by how this seems to contradict what your first post says:

"if column K of sheet 2 and sheet 1 display a "1", I want it to place a 0 in the formula cell."

and your most recent:

"If a client sees no growth ('0' in K) in the first and second sheets, it returns '1'. Here I would like to see '0' because no growth occurred."

Perhaps you might clarify what a 1 and a 0 mean in column K, as well as column L (your formula result).

Cheers,

If I've understood, could you not perhaps simplify to something like the below ?

Code:

FY07!L5: =MAX(0,$K5-SUMIF('FY 06'!$B:$B,$B5,'FY 06'!$K:$K))

The above will take current growth (0/1) and subtract from that the growth figure on FY06 sheet

So to surmise you end up with a truth table like

Code:

	prior	
current	0	1
0	0	-1
1	1	0

The use of MAX ensures it never drops below 0, ie current growth = 0 and prior growth = 1 would otherwise generate -1

If the FY07 name does not exist on FY06 the SUMIF will return 0 by default.

I see no reason why the prior SUMIF based suggestion won't work for you...*

It matters not one iota to the SUMIF approach as to where a given person resides on the prior sheet nor if they fail to reside on the sheet at all - it will simply return the associated K value (0 if they do not exist)

Other than that I'm afraid I can't help - everything else would appear to be over engineering.

Did you try the suggestion ?

*that being said - if the client appears Multiple times on the prior sheet that's different - but if that's the case the existing route you're taking would fail also - you'd need to revert to a LOOKUP based approach

Originally Posted by dforte

If there were additional sheets, i.e. FY 10, 11, and I want to do the same check through multiple sheets, how would I accomplish this? Sumifs?

No, I'm afraid not. You can not conduct conditional calculations in 3D easily.

Can you outline what you want to do with the multiple sheets as clearly as possible ?

To me it seems that the if the name appears on the prior sheet (FY06) then all other sheets prior to that point (eg FY04, FY05) are irrelevant - ie the prior year stipulates current growth (and is itself by it's design based upon on FY05, in turn dependent upon FY04)

The sheets prior to prior year are only seemingly important if the client did not appear on the prior sheet (FY06) but did appear on either of FY04 / FY05 given you would need to base your FY07 calc on whichever was the last sheet the client appeared in, is that correct ?

If so... my first instinct would be to use a common range on each sheet which keeps a running check on "current" growth as at that year for each client - using the prior sheet client listing contents plus any "new" clients to populate the current client listing...

However to be clear without VBA (ie formulae alone) this is a non-trivial exercise.

The other alternative is to use VBA and create a User Defined Function to calculate this which iterates sheets backwards until such time as client is found.

If you could provide a more detailed sample file which illustrates setup and desired results that would be helpful.