Converting formula to column

Options

Hello,

I'am experiencing an issue in converting my formula to a column:

In one of my sheet the following formula is applied:

=IFERROR(VLOOKUP(Facture@row; {Copie de S1 2024 06 05 2024 Plage 1}; 1; false); 0)

⇒ I am able to convert this formula without generating an error

In another sheet, I nearly applied the same formula:

=IFERROR(VLOOKUP(Facture@row; {1.3. Clients non soldés SEMESTRE 1 2024 - Plage 2}; 1; false); 0)

but in this case, i can not convert my formula to the column, otherwise the error occured. I can only drag and drop it / copy paste it.

The number of rows being important, it would be nice to be able to convert it.

Anyone have a solution?

Thank you.

Tom

Answers

  • Tom Del
    Options

    Hello @Paul Newcome ,

    In my first image here, i apply my formula manually.

    In this second screenshot, i apply the "convert" to all my column. I have 0 result.

    In another sheet, I apply the exact same formula, and i am able to convert the formula to my column.

  • MarceHolzhauzen
    MarceHolzhauzen ✭✭✭✭
    edited 07/03/24
    Options

    Hi @Tom Del

    This could possibly be because Vlookup looks for a specific column number (in your case column 1) - when you move this column around your Vlookup breaks. Your problem here could also be due to your table range - is it one column or many/

    Have you ever used INDEX and MATCH, or INDEX and COLLECT? These are more stable than Vlookup

    Here's the help article - https://help.smartsheet.com/function/match

    But, for your formulas to work it would look something like:

    index(Column 2 Header:Column 2 Header, match(Facture@row,{Copie de S1 2024 06 05 2024 Plage 1},0))

    or

    index(collect(Column 2 Header:Column 2 Header,{Copie de S1 2024 06 05 2024 Plage 1},Facture@row),1)

    You can add an Iferror around that if you need to.

    Hope this helps

    P.S. I'm assuming that {Copie de S1 2024 06 05 2024 Plage 1} is a single column named range

    Marcé Holzhauzen
    Dare to try

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you intending to just repeat the data in the [Facture] column?

  • Tom Del
    Options

    Hello @MarceHolzhauzen ,

    Thank you for your answer.

    When i try to apply this function: =INDEX(Facture:Facture; MATCH(Facture@row; {Copie de 1.3. Clients non soldés SEMESTRE Plage 1}; 0)) ⇒ I have a result different that the one expected (wrong "facture" number) and when I convert the formula it does not work at all.

    @Paul Newcome , No, I check if the invoice number (Facture in French) is already present in another smartsheet. If it is the case, I do not add it. If it is not the case, I add it to my other Smartsheet.

    So the possible values for my column are either (0) ; either the invoice number ⇒ if it is 0; i add this line into my other smartsheet; if it is the invoice number, i have nothing to do

  • MarceHolzhauzen
    MarceHolzhauzen ✭✭✭✭
    edited 07/03/24
    Options

    Hi @Tom Del

    This makes a lot of sense. Do I understand correctly that you are trying to see if your Facture number has already been included in sheet 2?

    Let's help by giving better names to the sheets ( i am guessing these names based on your column being an Unpaid Audit column)

    Sheet 1: Current Sheet
    Sheet 2: Copie de 1.3. Clients non soldés SEMESTRE

    What we are looking to achieve is to see if the Facture (Current Sheet) exists in Facture (Copie de 1.3. Clients non soldés SEMESTRE), correct?

    With this in mind, your formula should be as follows


    =INDEX({Copie de S1 2024 06 05 2024 Plage 1}, MATCH(facture@row,{Copie de S1 2024 06 05 2024 Plage 1}),0)

    where {Copie de S1 2024 06 05 2024 Plage 1} is the Facture column in Sheet 2: Copie de 1.3. Clients non soldés SEMESTRE

    This should return the invoice numbers like your screenshot nr 1.

    On a side note, here are the limitations of column formulas


    Marcé Holzhauzen
    Dare to try

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

  • Tom Del
    Options

    Hi,

    Your formula works now perfectly, but unfortunately i can not convert it into a column formula ⇒ result = #INVALID REF

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You can just use a flag column to indicate whether it is there or not with an IF/COUNTIFS combo which generally goes together a little easier than a VLOOKUP or INDEX/MATCH.

    =IF(COUNTIFS({Other Sheet Facture Column}; @cell = Facture@row) = 0; 1)