0

I'm parsing a lot of data in Python, and use pandas to format at write to excel.

I need to add one column in one of the excel sheets that uses XLOOKUP inn excel to find the row in column B of sheet exchange where the value in column A of exchange is equal to column A of the current sheet, so I want this in excel:

=XLOOKUP(AX, exchange!A:A, exchange!B:B, "Not Found", 0)*GX

where X(as in AX and GX) is the current row number.

I use this in python to add a column to the existing df:

        df['Columnn Name'] = df.apply(
            lambda x: f'=XLOOKUP(A{x.name + 2}, exchange!A:A, exchange!B:B, "Not Found", 0)*G{x.name + 2}', axis=1
        )

However, when opening the excel sheet, I see that the formula is:

=@XLOOKUP(A2, exchange!A:A, exchange!B:B, "Not Found", 0)*G2

so with an @ inserted immediately before the XLOOKUP, and the calculation doesn't work (it just shows #NAME? in the cell. If I manually remove the @, it works as it should. But why on earth is it inserting an @, and how do I make it stop?

Aephir
  • 157
  • 6
  • https://www.google.com/search?q=python+excel+at+sign+formula+site:stackoverflow.com You may need to write the formula using openpyxl or xlsxwriter directly, and also research their support for Dynamic Array formulas. – BigBen Jul 26 '23 at 14:57
  • Ahh, thanks. I think I was searching too narrowly, and didn't get any hits. – Aephir Jul 26 '23 at 15:13

1 Answers1

0

Apparently, you need to prefix XLOOKUP (and a few other excel functions) with _xlfn.. So the python code should be:

        df['Columnn Name'] = df.apply(
            lambda x: f'=_xlfn.XLOOKUP(A{x.name + 2}, exchange!A:A, exchange!B:B, "Not Found", 0)*G{x.name + 2}', axis=1
        )
Aephir
  • 157
  • 6