If Different Cells Match Then Add Value

[jaclrsen]

Hi, I've got a spreadsheet where people select the colour they think is going to win. The 'Outcome' sheet goes the outcome of the challenges and which colour won which challenge. The 'Board' sheet shows how many points someone is on. The 'Event' sheet show which colours the players selected.

So in challenge A, C, D, James chose the correct colour. I'm trying to add two points to James' points tally for each win. So essentially James should now have six points as he won three times. I'm just unsure of the formula to use to calculate the total points for each user.

You may notice the challenges in the 'Event' sheet are spaced apart because there is other data which goes in between and I also need this to work in Excel 2010. Please see attached. Any help would be great. Thanks.

[shukla.ankur281190]

Hi,

Use this formula in Event sheet's L3=IFERROR(IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!C1,Outcome!$A$5:$A$9,0))=C3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!E1,Outcome!$A$5:$A$9,0))=E3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!G1,Outcome!$A$5:$A$9,0))=G3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!I1,Outcome!$A$5:$A$9,0))=I3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!K1,Outcome!$A$5:$A$9,0))=K3,2,0),"")

in Event sheet L3

=IFERROR(IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!C1,Outcome!$A$5:$A$9,0))=C3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!E1,Outcome!$A$5:$A$9,0))=E3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!G1,Outcome!$A$5:$A$9,0))=G3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!I1,Outcome!$A$5:$A$9,0))=I3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!K1,Outcome!$A$5:$A$9,0))=K3,2,0),"")

Bord sheet C5=VLOOKUP($B5,Event!$B:$L,MATCH(Board!$C$3,Event!$B$1:$L$1,0),FALSE)

=VLOOKUP($B5,Event!$B:$L,MATCH(Board!$C$3,Event!$B$1:$L$1,0),FALSE)

you can also invisible the whole column L (event sheet) just enter "." in A1 cell

[jaclrsen]

Hi Shukla, Thanks for your response. However, I could not get this to work. Please note, I'm using Excel 2003 from home though so I don't know if you need 2010. The computer at work has 2010.

I've attached the file again to show you what I tried to do. Is there a formula which works for both versions? Thanks for your help again.

[shukla.ankur281190]

Copy of Sample5.xlsHey i am sorry i was attached the wrong 1.

Please find attached revised one.

[shukla.ankur281190]

You just need to remove Ifferror formula. use below one.

=IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!C1,Outcome!$A$5:$A$9,0))=C3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!E1,Outcome!$A$5:$A$9,0))=E3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!G1,Outcome!$A$5:$A$9,0))=G3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!I1,Outcome!$A$5:$A$9,0))=I3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!K1,Outcome!$A$5:$A$9,0))=K3,2,0)

or you can use below one

=IF(ISNA(IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!C1,Outcome!$A$5:$A$9,0))=C3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!E1,Outcome!$A$5:$A$9,0))=E3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!G1,Outcome!$A$5:$A$9,0))=G3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!I1,Outcome!$A$5:$A$9,0))=I3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!K1,Outcome!$A$5:$A$9,0))=K3,2,0)),"",IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!C1,Outcome!$A$5:$A$9,0))=C3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!E1,Outcome!$A$5:$A$9,0))=E3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!G1,Outcome!$A$5:$A$9,0))=G3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!I1,Outcome!$A$5:$A$9,0))=I3,2,0)+IF(INDEX(Outcome!$B$5:$B$9,MATCH(Event!K1,Outcome!$A$5:$A$9,0))=K3,2,0))

[JohnTopley]

Try

C5....

=VLOOKUP($B5,Event!$B3:$L10,11,FALSE)

And in L3 enter formula with Ctrl+Shift+Enter

Note that PURPLE in OUTCOME had an extra blank so you would not get a match.

You should now get a result of 6.

[jaclrsen]

Hi Shukla and John, Using both of your formulas worked very well and did exactly what I needed. Thank you so much. Just a question for both of you. Shukla, how did you make the 'L' Column invisible when putting in the '.'. I couldn't replicate this.

John, in the VLOOKUP formula, what does the '11' refer to? I'm thinking that B3:L10 is the range.

Thank you the both of you. Your help is really appreciated.

[shukla.ankur281190]

I have used conditional formatting for making L column invisible dear

Second Questing which is asking you to Mr. John => We need to tell Vlookup which column you need to show as result that is why Mr. John did 11 column reference. I did instead of 11 to Match function.

[jaclrsen]

Hi Shukla, It makes sense now. Thank you so much for that explanation and for all of your help. I'm grateful for John's help too. Thanks.

[shukla.ankur281190]

We glad to help you. Please mark it as solved and if you liked it make click on star for reputation.

[JohnTopley]

Glad to have helped. If you are OK with the results, can you mark the thread as SOLVED ("Thread Tools" on first posting).

Thank you.