Shorten nested IF Formula

[lukasvjohansson]

FYI - im working in excel version 1803, office 365, and in swedish.

This is my nested IF formula.

=IF(AND(ISNUMBER(SEARCH("ICA";D4));D2="STANDARD");ICA_STANDARD;
IF(AND(ISNUMBER(SEARCH("COOP";D4));D2="STANDARD");COOP_STANDARD;
IF(OCH(ISNUMBER(SEARCH("HEMKOP";D4));D2="STANDARD");AXFOOD_STANDARD;
IF(OCH(ISNUMBER(SEARCH("WILLYS";D4));D2="STANDARD");AXFOOD_STANDARD;
IF(OCH(ISNUMBER(SEARCH("CITYGROSS";D4));D2="STANDARD");BERGENDAHLS_STANDARD;0)))))

it could just as easilt have been made into an IFS formula ofc. The X_STANDARD are named ranges. Anyway, its too long to put into the source of a data validation list.

Any tips would be greatly appreciated!

Best regards

Lukas

 

[Fluff]

Re: Help to shorten nested IF Formula

One way to shorten it would be to check D2 first, like
=IF(D2<>"STANDARD";0;if(ISNUMBER(SEARCH("ICA";D4));ICA_STANDARD;IF(ISNUMBER(SEARCH("COOP";D4));COOP_STANDARD;

 

[lukasvjohansson]

Re: Help to shorten nested IF Formula

Thanks for the suggestion! However, I now realise that one reason for my need to shorten the formula is that I will have even more arguments following "STANDARD", like "MEDIUM", followed by e.g. "ICA_MEDIUM" and so on.

 

[Fluff]

Re: Help to shorten nested IF Formula

You can still use a similar idea like
=IF(D2="Standard",IF(E2="ICA","Std_ICA",IF(E2="MCB","Std_MCB")),IF(D2="Medium",IF(E2="ICA","Med_ICA",IF(E2="MCB","Med_MCB"))))
Although it may still be too large for DV

 

[Eric W]

Re: Help to shorten nested IF Formula

I'm not entirely sure what you need with your latest requirement, but here's an option that could be adapted. Consider this layout:

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
Code	Range		a	f	I	mm
STANDARD	ICA	ICA_STANDARD	b	g	J	nn
COOP	COOP_STANDARD		c	h	K	oo
maxicamax	HEMKOP	AXFOOD_STANDARD	d	L	pp
WILLYS	AXFOOD_STANDARD	e	qq
d	CITYGROSS	BERGENDAHLS_STANDARD	rr

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet20​

Build a table like I have in I1:J6. That has the code you are looking for, plus the named range you want to use if found. Here N1:N5 is a named range "ICA_STANDARD", O1:O3 is "COOP_STANDARD", etc.

I put my Data Validation in D6 with List as the type, and this as the source:

=IF(D2="STANDARD";INDIRECT(LOOKUP(2^15;SEARCH($I$2:$I$6;D4);$J$2:$J$6));"")


If I understand your request, this should do what you want. It's easy enough to change the table if you want. You can even change the table references from I2:J6 to I2:J20 if you want to leave room for later additions, but if you do, put XXXX in I7:I20, or something that will not be found in D4, otherwise the formula will fail. Hope this helps.