Formula required for Conditional formatting based on a range of different data and cell

Good afternoon,

I have a further request after my last request was suitably answered. I am now trying to find the best formula to use that accesses information from a range of cells across two different sheets and conditionally formats a cell based on the outcome.

I have also attached a reference sheet. I think I possibly need to use the Vlookup function and also range but I'm unsure on how to combine them to conditionally format.

Basically what I would like to achieve is. We have two different aircraft. Between a range of altitudes they have different minimum speeds. between 0 and 5000ft the minimum speed for 1 aircraft is 90 knots. Between 5000-10000ft the minimum speed is 100kts etc as per the attached example sheet. However, what I would like to do is in the input section. I would like to select the aircraft type in Cell B1, then select the operating altitude in Cell B2 and the requested speed in B3. Basically if the speed requested is lower than that shown in tab 2 between the operating altitudes I would like to be able to highlight the cell red.

For example. In B1 the P68 is selected. In B2 the operating altitude is typed in for example 1500ft and in B3 the requested speed is type in for example 80kts. On the second tab it can be seen that between 0 and 5000ft the minimum speed is 90kts so in this instance cell B3 would show up as red indicating too slow.

Many thanks in anticipation of any assistance.

james

Try

in B3

=INDEX('Speeds and altitude ranges'!B2:F3,MATCH(Input!B1,'Speeds and altitude ranges'!A2:A3,0),MATCH(Input!B2,'Speeds and altitude ranges'!B1:F1,1))

B1, C1 in ('Speeds and altitude ranges' have values of 0,5000,10000,15000,20000

Hi John,

Thanks for that. That is the sort of thing that I want to be able to do but Cell B3 I want to be able to actually type in the speed manually and use a conditional formatting to highlight whether the speed is above the minimum range. Although the other way around this is if there is not a way to do it by conditional formatting then I can use your suggestion to find what the minimum speed and then use conditional formatting in the cell to highlight whether the requested speed is above or below the minimum.

Thanks

For CF

Use a formula ......

Enter the following

=$B$3>=INDEX('Speeds and altitude ranges'!$B$2:$F$3,MATCH($B$1,'Speeds and altitude ranges'!$A$2:$A$3,0),MATCH($B$2,'Speeds and altitude ranges'!$B$1:$F$1,1))

Then FORMAT==>FILL> ....


Changes ranges in formula to suit