General Betting
There is currently 1 person viewing this thread.
I'm not sure whether it's possible to do the following, but here goes
=IF(G2=1,IF(B2=$B$1,VLOOKUP(E2,Index,10,IF(E2=$B$1,VLOOKUP(B2,Index,9,)))),"")
I have a list of football results, and I'm trying to retrieve a rating for team B1's opponents.
When B1 are at home (b2=b1) it is correctly looking up the rating for opponent E2.
When B1 are away it returns FALSE when it should be looking up the rating for team B2 from table index in column 9.
What is wrong with the above formula.
=IF(G2=1,IF(B2=$B$1,VLOOKUP(E2,Index,10,IF(E2=$B$1,VLOOKUP(B2,Index,9,)))),"")
I have a list of football results, and I'm trying to retrieve a rating for team B1's opponents.
When B1 are at home (b2=b1) it is correctly looking up the rating for opponent E2.
When B1 are away it returns FALSE when it should be looking up the rating for team B2 from table index in column 9.
What is wrong with the above formula.
sort by:
Show
per page
Replies: 8
|
By:
=IF(G14=1,IF(B14=$B$1,VLOOKUP(E14,Index,10,IF(E14=$B$1,VLOOKUP(B14,Index,9,)))),"")
That worked. |
|
By:
=IF(G2=1,IF(B2=$B$1,VLOOKUP(E2,Index,10,FALSE),IF(E2=$B$1,VLOOKUP(B2,Index,9,))),"")
sorry this is the one that worked |
|
By:
I am not to sure what you are doing ~ But ~
You have 2 IF's in a sequence without telling the first IF what to do. If you want both G2=1 and at the same time B2=$B$1 you need to use AND as below =IF(AND(G2=1,B2=$B$1),VLOOKUP(E2,Index,10,FALSE),IF(E2=$B$1,VLOOKUP(B2,Index,9,)),"") Hope that is of some help. |
|
By:
That's neat CM.
(I usually use the OPs method of having nested IFs if I need more than one true. Only posting this in case, for some reason, you didn't know you could do that... although your method is nicer I think) |
|
By:
So you have IF(a=1,(IF b=1.......,middle bracket after comma),final term)
For example. If a=1 then it processes the second IF. If a=1 and b is not =1 then it processes the middle bracket after the comma If a is not =1 then it processes the final term. |
|
By:
After the 9,(at the end) it should also indicate what needs to happen, maybe another false
|
|
By:
This looks more right
=IF(AND(G2=1,B2=$B$1),VLOOKUP(E2,Index,10),IF(E2=$B$1,VLOOKUP(B2,Index,9),"")) |
|
By:
Cheers for taking the time both of you.
The one I'm using is working, more by chance, but i'll keep your suggestions close by for future use. |