Index Match Multiple Columns

[Noz2k]

So I have a formula at the moment, which searches for a match with some given criteria, but only in column C, I'd like it to also look in column D, and return if either C or D match.

=iferror(INDEX('Fixtures & Results'!A:A,XMATCH(1,('Fixtures & Results'!B:B>=TODAY())*('Fixtures & Results'!C:C=E2),0,1)),"n/a")

 

[jasonb75]

Try

=INDEX(FILTER('Fixtures & Results'!A:A,(('Fixtures & Results'!B:B>=TODAY())*(('Fixtures & Results'!C:C=E2)+('Fixtures & Results'!D:D=E2)),"n/a"),1)

Also, try reducing the range sizes to something realistically proportionate to the amount of data in your sheet. Full column formulas are far from efficient and using TODAY() makes it volatile which is even worse. Do you notice delay while it calculates every time you change something in your sheet?

 

[Noz2k]

Thanks, I tried it, but it returns as an Error.

Also tried encapsulating it in an OR

OR(('Fixtures & Results'!C:C=E2),('Fixtures & Results'!D:D=E2))

but it doesn't like that either.

The range sizes are set as such because the number of rows can be added to by the front end, and at the moment at least it isn't noticeably delaying anything, calculations are pretty instantaneous.

 

[jasonb75]

Sorry, too many parentheses. Copying and pasting from your formula then editing it, I left one in that should have been removed.

=INDEX(FILTER('Fixtures & Results'!A:A,('Fixtures & Results'!B:B>=TODAY())*(('Fixtures & Results'!C:C=E2)+('Fixtures & Results'!D:D=E2)),"n/a"),1)

 

[Noz2k]

That returned n/a, as I think you had a bit of my iferror left in there. Changed it slightly though and got it working from your formula, so massive help, thank you.

=iferror(INDEX(FILTER('Fixtures & Results'!A:A,('Fixtures & Results'!B:B>=TODAY())*(('Fixtures & Results'!C:C=E2)+('Fixtures & Results'!D:D=E2))),1),"n/a")

 

[jasonb75]

Not critical as you have it doing what you need anyway but it should have worked correctly as it was, the "n/a" that I left in should only be shown when there are no results, so it should do the same as, and shouldn't need IFERROR.

If you do find it getting slow then this should help, it starts by finding the last row with a date in column B, then only process up to that point instead of including the empty rows below.

=LET(lr,MATCH(1E+100,$B:$B),rng,$A$2:INDEX($D:$D,lr),INDEX(FILTER(rng A:A,(rng B:B>=TODAY())*((rng C:C=E2)+(rng D:D=E2)),"n/a"),1))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Noz2k]

Thank you for the help in optimising, that is useful to know for the future anyway regardless if I use it for this specific instance.

It shouldn't have produced n/a though because there are definitely results which match the criteria in the dataset (that are now showing).