Could someone help me turn the following equation in Column I into one where it does not use Vlookup but rather Index/Match functions and gives me desired result please. I am struggling with it and getting some #N/A values.
The formula in column I6 currently is:
=IF(AND(E6="fav",(VLOOKUP(D6,B6:C7,2,FALSE)-F6)>VLOOKUP(H6,B6:C7,2,FALSE)),"W",IF(AND(E6="dog",D6=G6),"W",IF(AND(E6="dog",D6=H6,((VLOOKUP(D6,B6:C7,2,FALSE)+F6)>VLOOKUP(G6,B6:C7,2,FALSE))),"W","L")))
I want to have a formula in column/cell I6 that uses Index/Match functions and returns a "W" if at least one of the 3 scenarios below is true. If at least one of them are not true, then return "L" in cell I6:
If the result of part A above is > value recorded in part B, then return "W" in I6, else go to step #2 below
If E6 has the text "dog" AND cell D6 = G6, then return a "W" in cell I6. Else go to step #3 below
If E6 has the text "dog" AND cell D6 = H6, then the following equation described in part C and D below has to be true in order to return a "W" in cell I6 . C. Lookup the name located in cell D6 inside of column B6:B7 and take that score that belongs to it and add the value in cell F6 to it. D. Then Lookup the name located in cell G6 inside of column B6:B7 and record its score from column C.
If the result of part C above is > value recorded in part D, then return "W" in I6,
else return "L" in cell I6.
I appreciate the help. Thanks.
I tried using different variations of Index and Match together but it worked for a couple of rows but returned #N/A for others. I do not think I am using the Index / Match function correctly when trying to add or subtract the score in F6 from it in order to get the results desired.
Assuming I understand correctly your problem:
=IF(AND(E6="fav",(INDEX(C6:C7,MATCH(D6,B6:B7,0))-F6)>INDEX(C6:C7,MATCH(H6,B6:B7,0))),"W",
IF(AND(E6="dog",D6=G6),"W",
IF(AND(E6="dog",D6=H6,(INDEX(C6:C7,MATCH(D6,B6:B7,0))+F6)>INDEX(C6:C7,MATCH(G6,B6:B7,0))),"W","L")))