"IF" Combo Formula, two part question

G

Guest

1. The below formula works great, however, I would like to do a little more
with it.

=IF(ISNUMBER(SEARCH("*EDI*",D2)),IF(ISNUMBER(SEARCH"*EDI*",F2)),"Julio","Mezar"))

Now, I have the following, how can I retune the correct name?
EDI EDI = Julio
EDI ML = Meza
PN EDI = Angelina

2. And can I add or combine the above formula to the below formula?

=INDEX('Cash App Schedule'!$B$2:$N$22,MATCH(C2,'Cash App
Schedule'!$A$2:$A$22,0),MATCH(E2,'Cash App Schedule'!$B$1:$N$1,0))
 
G

Guest

For your first question, if I understand correctly, try:

=CHOOSE(SUMPRODUCT(--(ISNUMBER(SEARCH({"EDI","EDI","PN"},D2))),--(ISNUMBER(SEARCH({"EDI","ML","EDI"},E2))),{1,2,3})+1,"","Julio","Meza","Angelina")

Note that if none of the combinations are found, it returns "".

For your second question, there's not really enough information to say. If
the above formula goes into cell C2, try replacing C2 in the formula below
w/the above formula and see what happens. But there is a lot to be said for
performing computations in stages to keep the formulae manageable. Monster
formulae can be a RPITA to debug or modify later on.
 
G

Guest

BTW - I am assuming there cannot be multiple combinations, such as

EDI EDI
and
EDI ML

both appearing in the data at the same time.
 
G

Guest

Oh, and I was trying to do something like this:

=CHOOSE(SUMPRODUCT(--(ISNUMBER(SEARCH({"EDI","EDI","PNC"},D274))),--(ISNUMBER(SEARCH({"EDI","ML","PNC"},F274))),{1,2,3})+1,"","Julio","Meza","Angelina"),INDEX('Cash
App Schedule'!$B$2:$O$20,MATCH(C273,'Cash App
Schedule'!$A$2:$A$20,0),MATCH(E273,'Cash App Schedule'!$B$1:$O$1,0))
 
G

Guest

To explain a little of what it is doing, assume you have

D E
274 EDI ML

First, we use SEARCH({"EDI","EDI","PNC"},D274) which searches for "EDI",
"EDI", and "PNC" in D274 and will return the starting character position or
an error for each item we're searching for (in the form of a 1x3 array). So
we get

1 1 #VALUE

But what we want to know is simply if it found the text or not (regardless
of character starting position and w/o getting the #VALUE), so we can use
Isnumber to differentiate between the numbers and errors returned by search

--(ISNUMBER(SEARCH({"EDI","EDI","PNC"},D274)))

The "--" is the double unary operator. Excel stores TRUE as 1 and FALSE as
0. When you perform a mathematical operation w/TRUE & FALSE, excel converts
them to their underlying values of 1 and 0. The double negative simply
coerces TRUE/FALSE values to 1/0. So you get:

1 1 0

The same is done for cell E274 using
--(ISNUMBER(SEARCH({"EDI","ML","PNC"},F274))). So now we have:

1 1 0
0 1 0

The third argument of Sumproduct is a 1x3 array of 1,2,3. So our array
looks like:

1 1 0
0 1 0
1 2 3

Sumproduct will multiply these three arrays together, then add up the
results. Multipling the three together and adding gets us

0 2 0 = 2

what's left of the formula is
=CHOOSE(2+1,"","Julio","Meza","Angelina")

The first argument of Choose tells it which one of its other arguments to
return. Since Choose does not accept 0 (which sumproduct will return if
there are no matches) you have to add 1 to the results of sumproduct.

Bob Phillips covers sumproduct here
http://xldynamic.com/source/xld.SUMPRODUCT.html

BTW - if you only need to test your data to see if it is equal to "EDI",
search becomes unnecessary. For example, Search would be used to find "edi"
in the word "edible". If your data is only "edi", not some text that you
need to determine if it contains "edi" somewhere, the formula can be
shortened to:

=CHOOSE(SUMPRODUCT(--(D274={"EDI","EDI","PN"}),--(E274={"EDI","ML","EDI"}),{1,2,3})+1,"","Julio","Meza","Angelina")

I only used Search because you had it in your OP, so I only assume it is
needed.


I still don't follow how the results of the Choose function fit in the
formula below. I see that you are doing a 2D lookup, but let's say Choose
returns "Angelina". Where should that go in the formula below? If you want
it to replace C273, try copying the text of the choose function and pasting
it over C273.

INDEX('Cash App Schedule'!$B$2:$O$20,MATCH(C273,'Cash App
Schedule'!$A$2:$A$20,0),MATCH(E273,'Cash App Schedule'!$B$1:$O$1,0))

Or are the results of the Index function supposed to replace one the
arguments in the Choose function?
 
G

Guest

To explain a little of what it is doing, assume you have

D E
274 EDI ML

First, we use SEARCH({"EDI","EDI","PNC"},D274) which searches for "EDI",
"EDI", and "PNC" in D274 and will return the starting character position or
an error for each item we're searching for (in the form of a 1x3 array). So
we get

1 1 #VALUE

But what we want to know is simply if it found the text or not (regardless
of character starting position and w/o getting the #VALUE), so we can use
Isnumber to differentiate between the numbers and errors returned by search

--(ISNUMBER(SEARCH({"EDI","EDI","PNC"},D274)))

The "--" is the double unary operator. Excel stores TRUE as 1 and FALSE as
0. When you perform a mathematical operation w/TRUE & FALSE, excel converts
them to their underlying values of 1 and 0. The double negative simply
coerces TRUE/FALSE values to 1/0. So you get:

1 1 0

The same is done for cell E274 using
--(ISNUMBER(SEARCH({"EDI","ML","PNC"},F274))). So now we have:

1 1 0
0 1 0

The third argument of Sumproduct is a 1x3 array of 1,2,3. So our array
looks like:

1 1 0
0 1 0
1 2 3

Sumproduct will multiply these three arrays together, then add up the
results. Multipling the three together and adding gets us

0 2 0 = 2

what's left of the formula is
=CHOOSE(2+1,"","Julio","Meza","Angelina")

The first argument of Choose tells it which one of its other arguments to
return. Since Choose does not accept 0 (which sumproduct will return if
there are no matches) you have to add 1 to the results of sumproduct.

Bob Phillips covers sumproduct here
http://xldynamic.com/source/xld.SUMPRODUCT.html

BTW - if you only need to test your data to see if it is equal to "EDI",
search becomes unnecessary. For example, Search would be used to find "edi"
in the word "edible". If your data is only "edi", not some text that you
need to determine if it contains "edi" somewhere, the formula can be
shortened to:

=CHOOSE(SUMPRODUCT(--(D274={"EDI","EDI","PN"}),--(E274={"EDI","ML","EDI"}),{1,2,3})+1,"","Julio","Meza","Angelina")

I only used Search because you had it in your OP, so I only assume it is
needed.


I still don't follow how the results of the Choose function fit in the
formula below. I see that you are doing a 2D lookup, but let's say Choose
returns "Angelina". Where should that go in the formula below? If you want
it to replace C273, try copying the text of the choose function and pasting
it over C273.

INDEX('Cash App Schedule'!$B$2:$O$20,MATCH(C273,'Cash App
Schedule'!$A$2:$A$20,0),MATCH(E273,'Cash App Schedule'!$B$1:$O$1,0))

Or are the results of the Index function supposed to replace one the
arguments in the Choose function?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compare & align 2 cols of Employee Names in sorted alpha order withVBA 4
Help! Array formula for data that uses words instead of numbers 4
How does special formula apply to conditional formatting? 1
Gods of VB, I call on thy. 20
if(isna(match formula 8
Formula Problem - if statement 3
SUM and IF formula 1
Ranking values in multiple subsets using one single formula 1

Top