Can you string more than seven IF worskeet functions? 4

[PeanutB7]

Hello,

I am trying to string 18 IF worsheet Functions together in order to evaluate input data from a worskheet in order to establish a corresponding scope priority in column V. For example if given a "H" in column S, a "H" in column T, and a "L" in Column U using the line 285 from the look up chart below the assigned "Scope Priority" should be II. The lookup chart is preferred to accomodate easy changes rather than multiple change to countless formulas.

I have written the IF Function:

=IF(Q8=$S$270,IF(R8=$T$270,IF(S8=$U$270,$V$270,IF(Q8=$S$271,IF(R8=$T$271,IF(S8=$U$271,$V$271))))))

Q8, R8, S8, refer to the input data worksheet cells that are being analyzed in order to asses the scope priority.
This function works fine but is limited to the first two lines of the look up chart. As soon as I attempt to extend the IF Function for the remaining 16 lines of possibilities, it comes up as an error.

Does anyone have any suggestions?

S T U V
Materiality Risk Scope Priority
Line Ttl
270 L L L III
271 L L M III
272 L L H III
273 L M L III
274 L M M III
275 L M H III
276 L H L III
277 L H M III
278 L H H III
279 M M L III
280 M M M II
281 M M H I
282 M H L II
283 M H M I
284 M H H I
285 H H L II
286 H H M I
287 H H H I


Thank You in advance for your time.

John B.

 

[AnotherHiggins]

You can only have seven embedded IF functions. But you can have a whole lot by using
=if(A,X,Y)&if(B,X,Y)&if(C,X,Y)&....

But, here's a much better way to proceed:[COLOR=blue white]
=SumProduct(($S$270:$S$287=$Q$8)*($T$270:$T$287=$R$8)*($U$271:$U$287=$S$8)*($V$271:$V$287))[/color]

Note: Each range in the SumProduct must contain the same number of cells.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PeanutB7]

John Thank you for your quick response.

I have attempted to string the IF Functions with the '&' in betweeen but regretfully you recieve multiple results for each IF Function strung. Instead of '&' should it be 'OR'. I have already tried typing 'OR' in between each IF Function but it does not recognize the request.

I will try the other suggestion but I am not sure I fully understand the suggestion in that direction but I will play with it while I await your answer.

Thanks again - Mucho appreciated,

John B.

 

[Gavona]

The format should be:
Or(if(...),if(....))
And(if(...),if(....))

However the solution I would suggest is:
In R270 have the formula = s270&T270&U270
Copy this down to the bottom of your table.

Replace your if formula with
=vlookup(Q8&R8&s8,$R$270:$R$287,4,0))


Gavin

 

[AnotherHiggins]

John,

The SumProduct formula will find the record where
$S$270:$S$287 = $Q$8
[tab]and
$T$270:$T$287 = $R$8
[tab]and
$U$271:$U$287 = $S$8
[tab]then it will return the corresponding value in
$V$271:$V$287

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[fneily]

How about this:
Before the Priority column, I inserted another column. In this column I concatenated STU so for the first item in this column it would be LLL. Etc. Then in another cell, you could concatenate the users input, let's say it turns out to be LLL. Then do a Vlookup on the columns of the concatenation and the Priority to return the roman numeral.
eg. =VLOOKUP(J1,testnumbers,2,FALSE)
J1 is the users concatenation, testnumbers is the two column range, 2 returns the priority and False means the exact match. Works perfectly.
By the way, the list must be sorted ascending on the concatenation column.

 

[Gavona]

By the way, the list must be sorted ascending on the concatenation column.

If the last parameter of vlookup is False (or zero) then the lookup table does not need to be sorted.


Gavin

 

[PeanutB7]

Gavin,

I was still not able to get the Or function to work according to your suggestion:
The format should be:
Or(if(...),if(....))
And(if(...),if(....))

As an example should it be: =IF(Q8=$S$270,IF(R8=$T$270,IF(S8=$U$270,$V$270,Or(IF(Q8=$S$271,IF(R8=$T$271,IF(S8=$U$271,$V$271)Or(IF(Q8=$S$272,IF(R8=$T$272,IF(S8=$U$272,$V$272)Or(IF(Q8=$S$273,IF(R8=$T$273,IF(S8=$U$273,$V$273)))))))))))

For some reason this does not work for me. What am I still doing wrong?

I also tried the V Lookup and have the table below to show the changes:

S T U V
Materiality Risk Scope Priority
Line Ttl
270 LLL L L L III
271 LLM L L M III
272 LLH L L H III
273 LML L M L III
274 LMM L M M III
275 LMH L M H III
276 LHL L H L III
277 LHM L H M III
278 LHH L H H III
279 MML M M L III
280 MMM M M M II
281 MMH M M H I
282 MHL M H L II
283 MHM M H M I
284 MHH M H H I
285 HHL H H L II
286 HHM H H M I
287 HHH H H H I

I have inserted the suggested formula in my Scope Priority Cell:

=vlookup(Q8&R8&s8,$R$270:$R$287,4,0))

I believe there is one too many closing parenthesis but even after removing it I get a #REF! error message. I think I understand the lookup request but what does the 4,0 refer to? I need the lookup to match the given combination and insert the respective I, II, or III from Column V.


John

I have also tried the Sum Product suggestion and have not got that to work as well. Please forgive my failure but I would appreciate additional support.

Thanks all for your patience regarding this subject,

JB

 

[Zathras]

If you wish, you can go back to using an IF statement like this:

=IF(OR(Q8="L",Q8&R8&S8="MML"),"III",IF(OR(Q8&R8&S8="MMM",Q8&R8&S8="MHL",Q8&R8&S8="HHL"),"II","I"))

 

[fneily]

You can "look up" the vlookup function in the Insert function dialog box or help.
It's alot more versatile and easier to use then nested If's.
Vlookup(Cell to lookup,Range to look in,Column to Return,False or 0 for exact match)

For yours:
T8 = Q8&R8&s8
=vlookup(T8,$R$270:$W$287,6,0)

 

[fneily]

Oops. Should be:

=vlookup(T8,$S$270:$W$287,6,0)

 

[fneily]

Geeesh. Oops. Should be:

=vlookup(T8,$S$270:$W$287,5,0)

 

[mintjulep]

User defined function using select...case statement

 

[PeanutB7]

Thanks everyone for the help with my lookup table. It works Great!