MNB PHPExcelDeveloper Guide
v2.0.5
Documentation versions v2.0.5Current v2.0.4Maintenance v2.0.3Maintenance v2.0.0Initial v2 Compare versions →

Lookup examples

Lookup Excel examples, explained clearly.

Open a focused example to see the implementation path, copy-ready PHP, expected workbook behavior, and the production boundary that matters.

Open first example View all 92 examples
4examples in this category
21focused categories
5implementation paths
← All 92 examples
01
VLOOKUP

Look up a key in the first column of a vertical table and return a value from another column.

MediumExcel formula
MnbExcel::formula()MnbExcel::fromWorkbookArray()
vlookup.php
<?php

use Mnb\PHPExcel\MnbExcel;

$orders = [
    ['SKU' => 'P-100', 'Product' => MnbExcel::formula('VLOOKUP(A2,Products!A:C,2,FALSE)', 'Laptop')],
];
$products = [
    ['SKU' => 'P-100', 'Product' => 'Laptop', 'Price' => 1249],
    ['SKU' => 'P-200', 'Product' => 'Monitor', 'Price' => 349],
];

MnbExcel::fromWorkbookArray(['Orders' => $orders, 'Products' => $products])
    ->withHeader()
    ->save('vlookup.xlsx');
02
HLOOKUP

Look up a value in the first row of a horizontal table.

MediumExcel formula
MnbExcel::formula()
hlookup.php
<?php

use Mnb\PHPExcel\MnbExcel;

$summary = [[
    'Band' => 'Standard',
    'Discount' => MnbExcel::formula('HLOOKUP(A2,Bands!B1:E2,2,FALSE)', 0.05),
]];
$bands = [
    ['Label' => 'Discount', 'Basic' => 0, 'Standard' => 0.05, 'Premium' => 0.10],
];

MnbExcel::fromWorkbookArray(['Summary' => $summary, 'Bands' => $bands])
    ->withHeader()
    ->percentageColumns(['Discount'])
    ->save('hlookup.xlsx');
03
XLOOKUP

Use a modern exact lookup that separates the lookup range from the return range and supports a not-found value.

AdvancedExcel formula
MnbExcel::formula()
xlookup.php
<?php

use Mnb\PHPExcel\MnbExcel;

$orders = [[
    'SKU' => 'P-200',
    'Price' => MnbExcel::formula('XLOOKUP(A2,Products!A:A,Products!C:C,"Unknown")', 349),
]];
$products = [
    ['SKU' => 'P-100', 'Product' => 'Laptop', 'Price' => 1249],
    ['SKU' => 'P-200', 'Product' => 'Monitor', 'Price' => 349],
];

MnbExcel::fromWorkbookArray(['Orders' => $orders, 'Products' => $products])
    ->withHeader()
    ->currencyColumns(['Price'], '$')
    ->save('xlookup.xlsx');
04
INDEX + MATCH

Combine MATCH to find a row and INDEX to return a value from a separate column.

AdvancedExcel formula
MnbExcel::formula()
index-match.php
<?php

use Mnb\PHPExcel\MnbExcel;

$orders = [[
    'SKU' => 'P-100',
    'Product' => MnbExcel::formula('INDEX(Products!B:B,MATCH(A2,Products!A:A,0))', 'Laptop'),
]];
$products = [
    ['SKU' => 'P-100', 'Product' => 'Laptop'],
    ['SKU' => 'P-200', 'Product' => 'Monitor'],
];

MnbExcel::fromWorkbookArray(['Orders' => $orders, 'Products' => $products])
    ->withHeader()
    ->save('index-match.xlsx');