Highlight Duplicates Between Multiple Columns

[WorkwearExp]

Hi,

I'm normally pretty good with Excel formulas but this one has got me stumped and wondered if any could help.

I'm running a football competition for my business where customers are required to guess World Cup football scores via Facebook. I'm storing all their entries in a spreadsheet.

In the spreadsheet column a is their name, b is the match (e.g. England v Italy), c is the home tame (England), d is the score (1-1), e is the away team (Italy)

Across from, that I have the actual fixtures taking place at the World Cup. So column I is the date, K is the home team (England), L is the actual score (2-0) and M is the away team (Italy)

What I need is a formula that when I enter the result on a game into the right set of columns (Columns I:M) it will highlight those Columns (Columns C:E) that have entered that correct score.

It needs to compare 3 columns and not just one because for example if it highlights the score (1:1 for example) it could be highlighting someone who picked Spain v Holland 1:1 where the correct result would be someone who picked England v Italy 1:1.

Does anyone know a formula I can use to do this?

Thanks,

John

[Glenn Kennedy]

I'd do it using two helper rows to make a unique entry for each result & then do an INDEX : MATCH. You can, of course, hide the helper rows.

See attached.

[WorkwearExp]

Hi,

That's great thank you.

However, does this only show one result? So if we were to have multiple people guess the same correct answer would it only show one rather than them all?

Thanks

[Glenn Kennedy]

You're completely right! That can be taken care of, but I'm sitting on a plane, about to take off. If no-one else provides the answer in the meantime, I'll do so after I get to my hotel in a couple of hours.

[WorkwearExp]

Thank you.

Sorry for taking up your time and enjoy your flight!

[Glenn Kennedy]

I hate being beaten. After a bumpy flight to London, I've agonised over this for an hour. BUT - IT WORKS. I hope.

If you move the columns around, the only thing that you MUST do, to make this ARRAY FORMULA work is to change the offset modifier (13 in muy equation - appearring twice) to be equal to the ccolumn number of the starting point of the array (so if you have the first answer cell in J, change the 13s to 10s.

Array formulae: set by CTRL + SHIFT + ENTER.

If this works, please mark the thread as solved and preferably hit the "Add Reputation" button. If it doesn't, well we can try again tomorrow afternoon in the airport!

[WorkwearExp]

Thank you that works perfectly.

I apologise for taking up your time and humbly thank you for all of your help. You've really done a fantastic job.

[Glenn Kennedy]

You're welcome. Also on the plus side, I can now do something on Excel that I couldn't do this time yesterday!