Looking back through cells in a column to find Specific Text(s)

Good morning,

In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2.

eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2.

If the Destination is not LAX, then Column E equals Column D.

the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.

Hope you can assist, and thanks in advance

Darren

In E2 enter:

=IF(TRIM(D2)="","",D2/IF(LOOKUP(REPT("z",255),$B$1:B2)="LAX",2,1))

then copy it right down.

You would adapt it like this...

=IF(TRIM(D2)="","",D2/IF(OR(LOOKUP(REPT("z",255),$B$1:B2)={"LAX","MEL"}),2,1))

you can add more strings within the curly brackets if desired.

Note that the LOOKUP(REPT("Z",255),B$1:B2) function finds the last text string in a range that is less than or equal to a string of "Z" repeated 255. Lookup() looks for the last value that is smaller than or equal to the lookup value... since it is unlikely you will have a bunch of "Z" 's in a cell, than it would make sense that the last text string it comes across in the range, is the string you want... the rest of the formula is I think logical...

The formula doesn't work, because your blanks in column A are not true blanks, and the formula picks that up...

So then try:

=IF(LEFT(LOOKUP(2,1/($A$6:$A7<>""),$A$6:A7),4)={"PHUK","NADI"},0.5,1) in F7, copied down.

Note: This is not an "array formula" and hence only need to confirm with ENTER.

I apologize...

Should have been:

=IF(OR(LEFT(LOOKUP(2,1/($A$6:$A7<>""),$A$6:A7),4)={"PHUK","NADI"}),0.5,1)

copied down.