I am trying to create a way to scan through the 'Trades Taken' tab looking for rows marked 'exit' or 'exit 2', depending on the column the formula is in, and then if the trade id on Overall Trade Log is equal to the trade id on the 'Trades Taken' page then return the price value from trades taken column D for that row. Here is an example spreadsheet so you can see what I am referring to: https://docs.google.com/spreadsheets/d/1Y8QHrARyYjJKfOwq0g3waDfkVyWbWc2uSIzBiqsIal0/edit?usp=sharing .

CodePudding user response：

Based on the data you provided you could also do something like this in G2

=ArrayFormula(ifna(VLookup(Filter(A2:A,A2:A<>"")&{G1,H1},{'Trades Taken'!A:A&'Trades Taken'!B:B&if(RegexMatch('Trades Taken'!B:B,"^Exit$")," 1",),'Trades Taken'!D:D},2,0)))

Though I recommend either changing Exit 1 to Exit in 'Overall Trade Log' or Exit to Exit 1 in 'Trades Taken'. Then the formula can be simplified to:

=ArrayFormula(ifna(VLookup(filter(A2:A,A2:A<>"")&{G1,H1},{'Trades Taken'!A:A&'Trades Taken'!B:B,'Trades Taken'!D:D},2,0)))

You can also replace the Filter(A2:A,A2:A<>"") with A2:A but this will slow down the formula a bit.

=ArrayFormula(ifna(VLookup(A2:A&{G1,H1},{'Trades Taken'!A:A&'Trades Taken'!B:B,'Trades Taken'!D:D},2,0)))