Forum Discussion

Alaa_Ajaj's avatar
Alaa_Ajaj
Copper Contributor
Mar 27, 2023

Search for text using nested IF

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

 

 

excel
Formulas and Functions

6 Replies

Sort By
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 27, 2023

    Alaa_Ajaj 

    =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.

     

    • Aladdin_Ajaj's avatar
      Aladdin_Ajaj
      Copper Contributor
      Mar 27, 2023
      I tested the formula it returned the values I needed, but for the blank cells I need to enter data manually I got an errors while I fill the cells is there anyway that the formula doesn't affect the blank cells?
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Mar 27, 2023

        Aladdin_Ajaj 

        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.

Resources