Is there a more efficient formula than this monster?

K

Kevin

Hi, I will try explain the situation as best i can so i hope this makes sense. I have a wb with over 30 sheets. each sheet has 8 league tables on it. I also have a sheet that summarises the ranking of the particular team i am interested in in all the tables. each table has the top 15 teams and a blank line for when my team is not in the top 15 where it fills the space and has its rank on the left eg 26 XYX. my summary page will then have XYX 26 or if we were in the top 15 XYZ 6. Finally if my team didn't place it must say NR. Here is the formula I used. it works fine but I was wondering if there is an easier one. I tried a vlookup but because the rank is to the left of the team names it doesn't seem to work.

=IF(ISERROR(IF(MATCH("XYZ",G10:G25,0)<15,,OFFSET(INDEX(G10:G25,MATCH("XYZ",G10:G25,0),1),,-1))),"NR",IF(MATCH("XYZ",G10:G25,0)<15,,OFFSET(INDEX(G10:G25,MATCH("XYZ",G10:G25,0),1),,-1))

Thanks in advanc
kevin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with formula for finding last greater than/less than or equal to value 2
Multiple Lookup as condition in sumproduct formula 2
Only Calculating Numerics 5
Comparing then populating 1
Formula not being Friendly 3
how to check More than one criteria and get the value 1
Rows to table 0
using an IF or AND function to get next largest value in list 1

Top