Mar 27 2023 12:33 PM - edited Mar 27 2023 12:38 PM
I wanna use nested IF(AND(SERACH formula to find the first letter on the left in B:B column based on the statues on C:C column and return the product name (SONY and VOLVO) in Condition 1 column if it's sell and condition 2 column if it's buy
Mar 27 2023 12:56 PM
=IF(AND(LEFT($B3,1)="V",$C3="Sell",ISEVEN(COLUMN())),"Volvo",IF(AND(LEFT($B3,1)="S",$C3="Sell",ISEVEN(COLUMN())),"Sony",IF(AND(LEFT($B3,1)="V",$C3="Buy",ISODD(COLUMN())),"Volvo",IF(AND(LEFT($B3,1)="S",$C3="Buy",ISODD(COLUMN())),"Sony",""))))
You can try this nested IF formula.
Mar 27 2023 02:02 PM
hi,
try this
=CHOOSE(($C3=D$2)*((LEFT($B3,1)="v")+(LEFT($B3,1)="s")*2)+1,"","volvo","Sony")
Mar 27 2023 02:36 PM
Mar 27 2023 03:19 PM
I'm not sure what the problem with the blank cells is. In the attached file is an example with a dynamic table and a formula for buy and a formula for sell and they seem to return the intended result. With 2 formulas we don't need ISEVEN(COLUMN()) and ISODD(COLUMN()) anymore because these formulas aren't copied across columns D and E.
The solution by @Hecatonchire works as expected in my sheet in columns G and H.