This follows on from my 2 previous questions: Excel Match multiple criteria and Excel find cells from range where search value is within the cell. Apologies if I'm over-posting but I figured each question is slightly different and bothering people for follow-up questions doesn't seem fair if I can't mark their answer as correct.
I have this working piece of code which checks 3 columns of data to return a value from range1 if all of the criteria are met:
=INDEX(range1,MATCH(1,(A2=range2)*(B2=range3)*(SEARCH(range4,J2)),0))
However I need the SEARCH option to work if a match is found in range4 OR range5, and thanks to John Bustos and Barry Houdini from this site I know how to use an OR command within a MATCH Function:
=INDEX(range1,MATCH(1,(A2=range2)*(B2=range3)*(((C2 = range4)+(D2 = range5))>0),0))
The code above works for exact matches but the Values of C2 and D2 are lists of numbers contained in single cells, and range4 and range5 are single years in each cell so the SEARCH function has to be used to check whether the single years are present within the list of years. So, judging by the two working pieces of code above I thought this would work:
=INDEX(range1,MATCH(1,(A2=range2)*(B2=range3)*(((SEARCH(range4,J2))+(SEARCH(range5,J2))>0)),0))
However, it does not, and nor does:
=INDEX(range1,MATCH(1,(A2=range2)*(B2=range3)*(((SEARCH(range4,J2))+(SEARCH(range5,J2)))),0))
I am remembering to Press CTRL+SHIFT+ENTER, it just always returns #N/A. I know it should be returning values as the first example works before I try and make it into an OR command.
I hope someone can shed some light on this. Thanks in advance,
Best Wishes,
Joe
The problem you get in changing to SEARCH is that with C2=range4 in previous version of the formula the result of that is either TRUE or FALSE whereas SEARCH(range4,J2) returns a number (position of range 4 value in J2) or a VALUE! error if it isn't in J2
...so if only one SEARCH finds a value and the other doesn't you'll still get the error generated by the one that doesn't and MATCH won't get a match, so the "OR" doesn't work........to fix that you need to add something to get the SEARCH to return TRUE or FALSE - you can do that with ISNUMBER, i.e.
=INDEX(range1,MATCH(1,(A2=range2)*(B2=range3)*(ISNUMBER(SEARCH(range4,J2))+ISNUMBER(SEARCH(range5,J2))>0),0))
Note also that the first formula you quote:
=INDEX(range1,MATCH(1,(A2=range2)*(B2=range3)*(SEARCH(range4,J2)),0))
may not work as intended because SEARCH might return a number other than 1, so you need ISNUMBER there as well, i.e.
=INDEX(range1,MATCH(1,(A2=range2)*(B2=range3)*ISNUMBER(SEARCH(range4,J2)),0))