CODEWITHSUNDEEP
excelindexingrangematchcell
MarkJ765
MarkJ765

Reputation: 1

Returning value above and below column range

I have a list of countries (B column) ordered by Rank (A column) in a Pivot Table. I have a drop down data validation list to select any one of these countries.

Once a country is selected I wish to automatically show the 5 countries below and 5 countries above the selected.

The A and B ranges go +5 and -5 above and below to try and return blanks if there is no country higher or lower than selected. I am currently using this formula for example;

 =INDEX(A:B,MATCH(Selected_Country,B,0)+1,COLUMN(B))

In particular this formula returns the next lowest country down the list as it is "+1" from the matched selected country. The "+1" can then be changed to "-1" and so on to show lower or higher countries in column B.

As this is set up to read from a Pivot Table, the column heading "Countries" and a few report filter headings are +5 above the highest country in the list and blank rows cannot be inserted to show blanks due to it being a Pivot Table.

This means that when I select the highest country in the list, it shows "Countries" as the next highest and so on. An IF statement based on the text e.g -IF "Countries" return blank- would have to also include all report filter titles and becomes messy.

Is there any other way to apply an IF statement which could omit these headings, maybe based on cell format?

Upvotes: 0

Views: 258

Answers (1)

Aditya Pansare
Aditya Pansare

Reputation: 1132

Please check this formula. I think you are seeking something like this only.

Enter this formula in "G5". arrange data as shown in image.

=OFFSET(INDIRECT(CELL("address",INDEX($B$2:$B$51,MATCH($G$3,$B$2:$B$51,0),0))),$F5,0)

enter image description here

Upvotes: 1

Related Questions