Create Dataset/Array for VLOOKUP Using PHPSpreadsheet

Question

I have a export.php file with a goal of setting data validation with a VLOOKUP function off of that. However, it seems I can't find anything in the Recipes that actually seem to support this. I thought I found something but it doesn't seem to work, or at least, it doesn't work how I'd like.

Here is my simplified code:

require_once 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$i = 3;

foreach ($_POST['foo'] as $key => $el)
{
    $sheet->setCellValue('D'. $i, '=VLOOKUP(C'. $i .',array,2,0)');
    $i++;
}

# Column A = labels
# Column B = value

# create range
$spreadsheet->addNamedRange(new \PhpOffice\PhpSpreadsheet\NamedRange('array', $spreadsheet->getActiveSheet(), 'A'));
$spreadsheet->addNamedRange(new \PhpOffice\PhpSpreadsheet\NamedRange('array', $spreadsheet->getActiveSheet(), 'B'));

# redirect browser output -> php://output (download)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="hello.xlsx"');
header('Cache-Control: max-age=0');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');

However, this doesn't work - the cell ranges fatal error as you can't select a column to add to the range.

How do I create a dataset for VLOOKUP using PHPSpreadsheet?

Thanks

Answer 1

The answer is to not use a dataset as such, but use a cell range:

$lastValueCell = 84; # this is dynamically got, but no value in showing how
$output[$partNum][3] = '=VLOOKUP(C'. $x .',Formulas!A$1:B$'. $lastValueCell .',2,0)';

# then append to spreadsheet using ->setCellValue()

VLOOKUP now works on export