Google-sheets – How to write this formula “If X = Y, TRUE, else(look at this range, find the position, then ”do this”)

google sheets

Since I don't know exactly the kind of function I'm looking for, be aware that maybe my terminology isn't right to describe what I need to do. I suppose that I need something close to an if/else statement but with an addition that I couldn't figure out so far.

I have a sheet like this:

example sheet

It shows the position of a team according to a certain statistic. But I need to know how far away on the final position on the championship they were at the end of the season. A team can be very good doing a certain thing, but not having all of it to make the first position, for example.

The final position in the championship is this one:

enter image description here

So what I need to do is something like:

if S2 = AF2, TRUE, if S2 is different than AF2, then look on AF2:AF21, and give me the number in AE2:AE21 that corresponds to the team on S2.

Best Answer

Without a helper column maybe: 

=if(match(S2,S:S,0)=match(S2,AF:AF,0),"TRUE",index(AE:AE,MATCH(S2,AF:AF,0)))

This is rather inelegant because of the "TRUE" override but basically uses MATCH to find what is effectively the row number to feed to the index function. You have not shown the results you require from your sample data but starting at the top I think the first three applying the above formula (and copying it down to suit) would be TRUE, 3 and 17.

Related Topic