enter image description here
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.
enter image description here
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")))