Converting formula to column

Tom Del

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

Paul Newcome

What error are you getting?

Tom Del

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

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

Paul Newcome

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

Tom Del

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

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

Tom Del

Hi,

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

Paul Newcome

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)