Select cell based on separate/non adjacent cell range

[RalphB1560]

Hello everyone,

I was hoping someone could please help me with a solution.

The image I have attached is the spreadsheet I am working with. The yellow shaded cell (0.07%) is conditionally formatted to highlight the cell yellow when the cell value falls between -0.05% and 0.1%, as it has done successfully. 

Based on the range (-0.05-0.1%)  0.07% occurs at $3.18 (first column on left).

My question is! 

Is there way for me to automate the process? Where instead of scrolling down and manually noting that 0.07% occurs at $3.18 can this dollar value auto fill into a cell on a summary page at the top of the table. As a result I can quickly identify that 0.07% occurs at a price of $3.18 rather than scrolling through the table.

Many thanks everyone. I am little bit out of my depth with the formula or potentially macro required?

Ralph B

[#2]

@RalphB1560 If your Excel recognises the dynamic array function FILTER, you can achieve this as demonstrated in the attached file. It will create a summary list of all rows that comply to the percent range you are interested in.

Alternatively, and since you already have conditionally formatted (yellow) the percentages that fall within the range, you could also filter the percent-column by color. This is also demonstrated in the attached file. It doesn't give you a summary table but it gives an instant overview without having to scroll down a long list.

[#3]

@Riny_van_Eekelen Thank you very much! ill give this a go now. 

I really appreciate your help.

Cheers

[#4]

@Riny_van_Eekelen Thank you that works great! problem solved :)

You are a legend! 

[#5]

@RalphB1560 Not really, but glad that I could help out!