How to use Vlookup function of excel in perl

Question

I have one excel file in that I want to compare A col and M column and if values are identical in both the column then shift/copy last column before 2nd column. I can do that in excel using vlookup function but I want to write a code in perl, but I don't know how to apply formula in perl. Here I have given input file which is .xlsx Thank you in advance

input_file.xlsx

YAL001C YAL001C 100 3483    0   0   1   3483    1   3483    0   6433    YLL054C 2532
YAL002W YAL002W 100 3825    0   0   1   3825    1   3825    0   7064    YDR374W-A   270
YAL003W YAL003W 100 621 0   0   1   621 1   621 0   1147    YIR038C 705
YAL004W YAL005C 100 648 0   0   1   648 672 25  0   1197    YMR252C 405
YAL004W YAL004W 100 648 0   0   1   648 1   648 0   1197    YDR168W 1521
YAL005C YAL005C 100 1929    0   0   1   1929    1   1929    0   3563    YOR117W 1305
YAL005C YAL004W 100 648 0   0   25  672 648 1   0   1197    YMR152W 1098
YAL007C YAL007C 100 648 0   0   1   648 1   648 0   1197    YJL138C 1188
YAL008W YAL008W 100 597 0   0   1   597 1   597 0   1103    YML073C 531
YAL009W YAL009W 100 780 0   0   1   780 1   780 0   1441    YBL089W 1380
YAL010C YAL010C 100 1482    0   0   1   1482    1   1482    0   2737    YOR038C 2628
YAL011W YAL011W 100 1878    0   0   1   1878    1   1878    0   3469    YOR220W 798
YAL012W YAL012W 100 1185    0   0   1   1185    1   1185    0   2189    YIR033W 3342
YAL013W YAL013W 100 1218    0   0   1   1218    1   1218    0   2250    YKL201C 3537
YAL014C YAL014C 100 768 0   0   1   768 1   768 0   1419    YPL213W 717
YAL015C YAL015C 100 1200    0   0   1   1200    1   1200    0   2217    YJL044C 1377
YAL016C-A   YAL016C-A   100 315 0   0   1   315 1   315 9E-167  582 YDR022C 591
YAL016C-A   YAL016W 100 191 0   0   1   191 191 1   8E-98   353 YOR053W 342
YAL016C-B   YAL016C-B   100 186 0   0   1   186 1   186 3E-95   344 YLR233C 2100
YAL016W YAL016W 100 1908    0   0   1   1908    1   1908    0   3524    YPR058W 924
YAL016W YAL016C-A   100 191 0   0   1   191 191 1   5E-97   353 YPL026C 1509
YAL017W YAL017W 100 4071    0   0   1   4071    1   4071    0   7518    YKL145W 1404
YAL018C YAL018C 100 978 0   0   1   978 1   978 0   1807    YKR091W 741
YAL019W YAL019W 100 3396    0   0   1   3396    1   3396    0   6272    YBR196C-A   150
YAL019W-A   YAL019W-A   100 570 0   0   1   570 1   570 0   1053    YER168C 1641
YAL019W-A   YAL020C 100 366 0   0   1   366 366 1   0   676 YOR306C 1566
YAL020C YAL020C 100 1002    0   0   1   1002    1   1002    0   1851    YML101C-A   318
YAL020C YAL019W-A   100 366 0   0   1   366 366 1   0   676 YBR131W 2115
YAL021C YAL021C 100 2514    0   0   1   2514    1   2514    0   4643    YDR522C 1509
YAL022C YAL022C 100 1554    0   0   1   1554    1   1554    0   2870    YGL179C 1683
YAL023C YAL023C 100 2280    0   0   1   2280    1   2280    0   4211    YAR030C 342
YAL024C YAL024C 100 4308    0   0   1   4308    1   4308    0   7956    YLL048C 4986
YAL025C YAL025C 100 921 0   0   1   921 1   921 0   1701    YER019C-A   267
YAL026C YAL026C 100 4068    0   0   1   4068    1   4068    0   7513    YLR204W 336
YAL026C YAL026C-A   100 194 0   0   3875    4068    1   194 2E-98   359 YBR045C 1920
YAL026C-A   YAL026C-A   100 438 0   0   1   438 1   438 0   809 YKL032C 1794
YAL026C-A   YAL026C 100 194 0   0   1   194 3875    4068    2E-99   359 YOR353C 2376
YAL027W YAL027W 100 786 0   0   1   786 1   786 0   1452    YGR109W-A   873
YAL028W YAL028W 100 1587    0   0   1   1587    1   1587    0   2931    YEL029C 939
YAL029C YAL029C 100 4416    0   0   1   4416    1   4416    0   8155    YBR246W 1164
YAL030W YAL030W 100 354 0   0   1   354 1   354 0   654 YCL005W 771
YAL031C YAL031C 100 2283    0   0   1   2283    1   2283    0   4217    YMR288W 2916
YAL031C YAL031W-A   100 229 0   0   2055    2283    309 81  5E-118  424 YGL019W 837
YAL031W-A   YAL031W-A   100 309 0   0   1   309 1   309 2E-163  571 YDR169C-A   150
YAL031W-A   YAL031C 100 229 0   0   81  309 2283    2055    6E-119  424 YDL169C 672
YAL032C YAL032C 100 1140    0   0   1   1140    1   1140    0   2106    YER114C 3123
YAL033W YAL033W 100 522 0   0   1   522 1   522 0   965 YOR032W-A   201
YAL034C YAL034C 100 1242    0   0   1   1242    1   1242    0   2294    YPL089C 2031
YAL034C-B   YAL034C-B   100 354 0   0   1   354 1   354 0   654 YLR313C 1593
YAL034C-B   YAL034W-A   100 125 0   0   1   125 125 1   4E-61   231 YHR187W 930
YAL034W-A   YAL034W-A   100 870 0   0   1   870 1   870 0   1607    YDR243C 1767
YAL034W-A   YAL034C-B   100 125 0   0   1   125 125 1   1E-60   231 YJR087W 351

Answer 1

If you want to process data contained in an xlsx file using only a perl program you can combine this two modules:
Spreadsheet::XLSX
Excel::Writer::XLSX
You can take the xlsx file containing original data and create a new one with the processed data.
The first module lets you read data from the excel file (so you can compare values), the second module lets you write a new excel spreadsheet, putting the values in the desired position.
You can not edit the original file.

If instead you want to create an excel file containing a vba function (macro) that will do the processing after the file opening you still have to use the modules listed above, but you'll probably have to deal with 3 files:
- the file containing the original data;
- a support xlsx file containing the vba function (that will be extracted and than imported into the new file);
- the output file where you will merge data and vba function.

Answer 2

Here is code: Sorry to post it as an answer because it wasn't taking complete code

use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::Read;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::XLSX;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('new_input.xls');

if ( !defined $workbook ) 
{
        die $parser->error(), ".\n";
}

my $worksheet; 
for  $worksheet ( $workbook->worksheets() ) 
{
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    for my $row ( $row_min .. $row_max ) 
    {
        for my $col ( $col_min .. $col_max ) 
        {
            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;
            #print "Row, Col    = ($row, $col)\n";
            #print "Value       = ", $cell->value(),       "\n";
            #print "Unformatted = ", $cell->unformatted(), "\n";
            #print "\n";
            my $formula = $worksheet->formula('=VLOOKUP($A$1:$A$6711,$M$1:$N$6711,2,0)');
        }
    }
}