Ranking and Lookup - Skip IF

[KrazyAl]

Hi

I have some data that I ranked and am trying to only show a certain portion of the data in a different location.

In the data I have a certain item I do not want to show in the end result, say "bananas". This week "bananas" are ranked #4. I have tried using a IF and Offset formula that would look at the item and if it was a match would go down one row.

=IF(AD28="Bananas",OFFSET(AC28,1,0),AC28)

Once it finds "bananas" it will show the #5 and then will show #5 again in the next field. In other words it does skip #4 but shows the next rank twice.

If bananas in list skip and show next ranking item.

The list updated every week any the rank is volatile.



Thanks in advance for your help!

 

[JLeno]

If there is only one entry of 'bananas', you could use the following:

=OFFSET(B2,COUNTIF(B$2:B2,"bananas"),0)

If there are possibly more than one encounters of bananas, this won't work, but I haven't figured out yet what the solution should be in that case. I guess the formula won't be pretty

 

[Drrellik]

your reference OFFSET(AC28,1,0) refers to cell AC29. of course you knew that, so IF AD28 is bananas then return what is in AC29 else AC28. I think I am only restating what you said, I guess I just don't follow the question. Don

 

[KrazyAl]

Thanks for the responses. I will try and clarify.

I have a list of 20 different items and I want to extract say the top 10 items and if one of the items is Bananas I do not want it to show.

I would prefer to have to have the rank as a result so I can run an index/match it

ORIGINAL
1 Oranges
2 Apples
3 Pears
4 Bananas
5 Kiwi

EXTRACT
1 Oranges
2 Apples
3 Pears
5 Kiwi


I hope I clarified my question

 

[KrazyAl]

Hi

Thanks for your solution JLeno.... I will be using this until, it will do for the time being!

Have a great day