This article shows how to use VLOOKUP in PHP with the latest version of PhpSpreadsheet. To begin with, it writes the formula into an Excel file and then reads the result straight back. First, a definition: VLOOKUP searches the first column of a table for a value and returns a cell from the same row — the classic “look this code up and give me its price” job. In fact, using VLOOKUP in PHP is the most-searched formula the site does not yet cover, alongside the SUM, AVERAGE and MAX posts.
The formula itself is simply an ordinary string you hand to setCellValue(), and PhpSpreadsheet then stores it exactly as Excel would. Still, one thing is worth getting right: the reference style. First, you write the lookup table with an absolute reference ($A$2:$C$5), so it keeps pointing at the whole table as you copy the formula down each order row. The cell you look up (E2), by contrast, stays relative, so it advances with the row. Get that backwards and every row looks up the same value, or the table slides off the bottom.
Then, to prove the formulas resolve without opening Excel, we read them back with getCalculatedValue(), which evaluates the formula — VLOOKUP included — on the PHP side. Below we first build a small product table and an orders table. Next we look up each order’s product name and line total, and finally print the results.
Requirements to use VLOOKUP in PHP:
- Composer
- PHP 8.2 or newer
Step 1.
First, set up the dependencies. Here we pin the latest major release of PhpSpreadsheet (the 5.x line, tested with 5.9 on PHP 8.2).
{
"require": {
"phpoffice/phpspreadsheet": "^5.0"
}
}Step 2.
Next, install phpspreadsheet.
$ composer install
Step 3.
Then create a new PHP file. Next, load Composer’s autoloader and import Spreadsheet and the Xlsx writer.
<?php require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
Step 4.
First, lay down the two tables. The first one is the lookup table — SKU, product name and unit price — and it is what VLOOKUP searches. The second is the orders table: each row names a SKU and a quantity, and for now leaves the product name and line total blank for the formulas to fill.
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle('Orders');
// The lookup table VLOOKUP searches: A2:C5.
$worksheet->fromArray([
['SKU', 'Product', 'Price'],
['K-100', 'Keyboard', 40],
['M-200', 'Monitor', 190],
['U-300', 'Mouse', 25],
['W-400', 'Webcam', 55],
], null, 'A1');
// The orders table: SKU and quantity are given; the rest is looked up.
$worksheet->fromArray([
['Order SKU', 'Qty', 'Product', 'Line total'],
['M-200', 2],
['K-100', 3],
['W-400', 1],
], null, 'E1');Step 5.
Next, write the formulas. Column G looks up the product name (column 2 of the table); column H then looks up the price (column 3) and multiplies it by the quantity in column F. The fourth argument, FALSE, forces an exact match — almost always what you want, and also the source of most VLOOKUP surprises when you leave it off. Finally, note the absolute $A$2:$C$5 and the relative E$row.
for ($row = 2; $row <= 4; $row++) {
$worksheet->setCellValue("G$row", "=VLOOKUP(E$row,\$A\$2:\$C\$5,2,FALSE)");
$worksheet->setCellValue("H$row", "=VLOOKUP(E$row,\$A\$2:\$C\$5,3,FALSE)*F$row");
}Step 6.
Save the workbook, then read the looked-up columns back with getCalculatedValue(). It evaluates each formula in PHP, so the product names and line totals appear without opening the file in Excel.
(new Xlsx($spreadsheet))->save('vlookup.xlsx');
echo "Wrote vlookup.xlsx\n\n";
printf("%-10s %-4s %-10s %s\n", 'SKU', 'Qty', 'Product', 'Line total');
for ($row = 2; $row <= 4; $row++) {
printf(
"%-10s %-4s %-10s %s\n",
$worksheet->getCell("E$row")->getValue(),
$worksheet->getCell("F$row")->getValue(),
$worksheet->getCell("G$row")->getCalculatedValue(),
$worksheet->getCell("H$row")->getCalculatedValue()
);
}Complete code to use VLOOKUP in PHP.
<?php require 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $worksheet = $spreadsheet->getActiveSheet(); $worksheet->setTitle('Orders'); // The lookup table VLOOKUP searches: A2:C5. $worksheet->fromArray([ ['SKU', 'Product', 'Price'], ['K-100', 'Keyboard', 40], ['M-200', 'Monitor', 190], ['U-300', 'Mouse', 25], ['W-400', 'Webcam', 55], ], null, 'A1'); // The orders table: SKU and quantity are given; the rest is looked up. $worksheet->fromArray([ ['Order SKU', 'Qty', 'Product', 'Line total'], ['M-200', 2], ['K-100', 3], ['W-400', 1], ], null, 'E1'); // Column G looks up the name, column H the price * quantity. // $A$2:$C$5 is absolute (the table stays put); E$row is relative. for ($row = 2; $row <= 4; $row++) { $worksheet->setCellValue("G$row", "=VLOOKUP(E$row,\$A\$2:\$C\$5,2,FALSE)"); $worksheet->setCellValue("H$row", "=VLOOKUP(E$row,\$A\$2:\$C\$5,3,FALSE)*F$row"); } (new Xlsx($spreadsheet))->save('vlookup.xlsx'); echo "Wrote vlookup.xlsx\n\n"; printf("%-10s %-4s %-10s %s\n", 'SKU', 'Qty', 'Product', 'Line total'); for ($row = 2; $row <= 4; $row++) { printf( "%-10s %-4s %-10s %s\n", $worksheet->getCell("E$row")->getValue(), $worksheet->getCell("F$row")->getValue(), $worksheet->getCell("G$row")->getCalculatedValue(), $worksheet->getCell("H$row")->getCalculatedValue() ); }
Test using VLOOKUP in PHP.
Command line testing.
$ php vlookup.php
Result of using VLOOKUP in PHP.
Here VLOOKUP looks up each order’s SKU in the product table. For example, M-200 resolves to Monitor at 190, so a quantity of 2 gives a line total of 380; K-100 is a Keyboard at 40, giving 120; and W-400 is a Webcam at 55, giving 55. You never typed the names and totals into the orders table — the formula filled them:
Wrote vlookup.xlsx SKU Qty Product Line total M-200 2 Monitor 380 K-100 3 Keyboard 120 W-400 1 Webcam 55
Finally, open vlookup.xlsx in Excel and columns G and H show the live =VLOOKUP(...) formulas; change a price in the table and the line totals update themselves. The same pattern also works for HLOOKUP (across a row) and, once you are on newer Excel, XLOOKUP.