Creating an excel function

[filo666]

Hi, I’m sick and tired always having to use VB when a Case structure is need,
how could I create a case structure; I want to appears like this:

“=case(a1=b1;ab1;a1=b2;c1;a1=b3;ab3;a1=b4;b1…..)†or
“=case(a1=b1;a1=b2;ab1;c1…..)â€

In English: When a1=b1 print ab1, when a1=b2 print c1 and so on.

So, how could I create my own case function?
PD. I know “=if†allow 9 condition, I don’t want use =if, =vlookup, =lookup
or =hlookup

TIA

 

[zackb]

Hi there,

Well, you are limited to 7 nested IF functions - not 9; 8 with the original.
And why not a LOOKUP? Is there something wrong with those? Why a UDF?
What does your data look like and what are the ranges in which you are
looking at? Please explain more.

 

[Duke Carey]

Why not use one of the lookups? Lots more equalities can be included, much
easier to maintain, etc, etc, etc

For your first example. assuming the conditions are mutually exclusive.

=(a1=b1)*ab1+(a1=b2)*c1+(a1=b3)*ab3+....

If they're not mutually exclusive then...good luck

 

[Tom Ogilvy]

Use the choose worksheet function.