2

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.

2

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.

0

=if(x-y, True, vlookup(s2, AE2:AF21,2,0))

except that for this to work, you would need to put the numbers in the 2nd column (AF) and the names in the first.

One way to do that it to make a hidden column (eg AG), in which every cell says =AE - and then change the lookup range to AF2:AG21.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.