Using or() within Index/Match?

Question

I am very familiar with Index/Match, but am trying something kind of new for me. I have a table of descriptions, amounts, and dates. I want to use index/match. Given a part of the description, and the lookup amount, I want to return the date.

Here's what my table looks like (and it's named range name below):

So, I want to use Index Match to look up the date:

Here's the formula in the Green Cell:

=INDEX(Dates,MATCH(F3&TRIM(MONTH($G3)&"/"&DAY($G3)),Amounts&TRIM(MID(Descriptions,SEARCH(" ",Descriptions),5)),0))

So, you can see that what I have correctly looked up 21.55, and 1-15-2015 and returned the date.

QUESTION: You will notice that in my Descriptions, some of the short dates use "/" and others "-". How can I build this into my formula? If I try to look up amount 993.29, and date 6/3/2015, I get an error. Since the description has a "-" instead of "/", Index/Match fails.

I am aware that I could do an IfError() statement, saying if the above formula returns an error, then do index/match with "-" instead:

=IFERROR(INDEX(Dates,MATCH(F3&TRIM(MONTH($G3)&"/"&DAY($G3)),Amounts&TRIM(MID(Descriptions,SEARCH(" ",Descriptions),5)),0)),INDEX(Dates,MATCH(F3&TRIM(MONTH($G3)&"-"&DAY($G3)),Amounts&TRIM(MID(Descriptions,SEARCH(" ",Descriptions),5)),0)))

But I would rather not do that, as this lookup is part of a bigger formula, and this just complicates that.

I was wondering if there's a way to do a formula like this:

INDEX(Dates,MATCH(F3&TRIM(MONTH($G3)&Or("-","/")&DAY($G3)),Amounts&TRIM(MID(Descriptions,SEARCH(" ",Descriptions),5)),0))

(Note: I added an "or()" after the first TRIM.

If that's not possible, am I stuck having to use an IF type statement? Or is there somehow an alternative, to keep the formulas relatively short?

Answer 1

Instead of your approach, I would suggest the following, also using Array formulas. keep in mind that Array formulas work by calculating a specific item multiple times over the distance of a given range. Instead of providing a single result, it provides an Array of results. In its core form, an example of an Array formula would be:

=if(A1:A10=1,"asdf","")

Type this into a cell, and confirm by pressing CTRL + SHIFT + ENTER, instead of just ENTER. Intuitively, you can see that in this formula, if A1=1, it will give "asdf", and if A2 = 1, it will give "asdf", etc. Assume A1 = 1, and A3 = 1, and all other cells is a different value. The result from this formula would be an array of results, as follows:

={"asdf","","asdf","","","","","","",""}

What it will show you in the cell is "asdf", because without further instruction it defaults to giving you the first response. Really what you need to do is wrap the array formula into a function which naturally accepts an array as an argument. For example, SUM takes an array of values, and adds them together.

In your case, what you want to do is to pull the row number where any row INCLUDES the text of your match date. To do this, we're going to create our own matching formula which uses IF, instead of MATCH. Like so [I have assumed that the date search cell is in H1].:

=IF(ISERROR(SEARCH(Day(H1)&"-"&Month(H1),B2:B5)),"",D2:D5)

If there is an error in a cell, then it didn't find it in the search, and doesn't give us the value in D. If it does give the value in D, that means the date was found within column B. Now you will notice here that this only checks for a date with "-" between day and month. To find it if the day and month is separated by a "/", we just add in a separate term which runs if it doesn't find the "-". Keep in mind that using OR / AND functions in Array formulas rarely does what you want, because OR / AND functions can accept array results into their arguments, and thus the arrays become collapsed into a single value.

Thus we manually create an "OR" by using the TRUE/FALSE options of the IF statement, like so:

=IF(ISERROR(SEARCH(DAY(H1)&"-"&MONTH(H1),B2:B5)),IF(ISERROR(SEARCH(DAY(H1)&"-"&MONTH(H1),B2:B5)),""),D2:D5)

This gives us the result from column D if either one matches. Remember to confirm with CTRL + SHIFT + ENTER, instead of just enter. Is it clean? No. If you want a clean formula I recommend you create a helper column In column E, which tries to create a date out of the text in column A, and then have your index/match (wouldn't need to be array formula anymore), just look at that refined date option.

EDIT

Shame on me, I didn't test this and it didn't work. I tested after, and realized I forgot some things. First I'll add that if you have an array formula, and you click inside of the formula bar, press F9 and it will show you the array of results that occur (if the response is left as an array). This will quickly help you find out if there is an 'intermediary' problem in resolving your array formula. In this case, if you have the formula above and you press F9, depending on what date you have selected, it will show you something like:

={"","",CORRECT RESULT,""}

To find the correct result, we do actually need to use the something to collapse the array, which, I forgot halfway through my post, was what I was leading up to.

So, MIN is a function which takes an array, and provides you with the lowest value of that array. Because RANK is able to collapse an array, we can modify the above so that it searches for the 'matched option', and gives that result. Something like:

=INDEX(D2:D5,MATCH(MIN(IF(ISERROR(SEARCH(DAY(H1)&"-"&MONTH(H1),B2:B5)),IF(ISERROR(SEARCH(DAY(H1)&"-"&MONTH(H1),B2:B5)),"",C2:C5),C2:C5)),C2:C5,0))

So you can see that instead of returning the Actual value from D that we were looking for, now the array function returns the dollar value associated with a specific item. We then match that item against all items and use the row number to pull the number from an indexed list.

The thing which makes this more complicated, requiring that last step, is that it is generally difficult to work with strings returned via Array without using VBA. Most Array-collapsing functions in excel require numbers, which we could have dealt with easily. This is... quite ugly. An alternative would be to say "Index(COMPLEX ARRAY,Match(KEY RESULT FROM COMPLEX ARRAY,INITIAL SEARCH ARRAY,0)), but that to me is more complex than doing the above.

Sorry for the premature codulation there. Should have tested first.

Answer 2

This succinct array formula accounts for both variations of description-embedded dates:

=INDEX(Dates,MATCH(1,(F3=Amounts)*(G3=--MID(Descriptions,1+FIND(" ",Descriptions),5)),))