PHP equivalent of Excel vlookup on array

Question

After looking for a built in function in php I couldn't find a similar function to Excel's vlookup function.

I need a function that takes in an array and a lookup value and return the required info. So for example:

<?php
$baseCalculationPrice = [
    0 => 50,        //for values <=500 but >0
    500 => 18,      //for values <=3000 but >500
    3000 => 15,     //for values <=5000 but >3000
    5000 => 14,     //for values >5000 
];

//Examples
    $numPages = 499;
    echo vlookup($numPages,$baseCalculationPrice); //should output 50
    $numPages = 500;
    echo vlookup($numPages,$baseCalculationPrice); //should output 50
    $numPages = 501;
    echo vlookup($numPages,$baseCalculationPrice); //should output 18
    $numPages = 3000;
    echo vlookup($numPages,$baseCalculationPrice); //should output 18
    $numPages = 3001;
    echo vlookup($numPages,$baseCalculationPrice); //should output 15
    $numPages = 5000;
    echo vlookup($numPages,$baseCalculationPrice); //should output 15
    $numPages = 5001;
    echo vlookup($numPages,$baseCalculationPrice); //should output 14

function vlookup($value,$array){
    //magic code

    return ....;
}
?>

I'm stuck even with the logic behind such a function, so any help would be great - thanks.

In my research I came across this stackoverflow.com/questions/16234992/… . However as it needs to work for values in between I couldn't find use of it - maybe others might — jamesmstone
– jamesmstone, Commented May 11, 2014 at 2:24
There is nothing like that in PHP. The closest would be array_searhc(), and it only does exact matches. YOu'll have to sort the array, then loop to find the nearest keys. — Marc B
– Marc B, Commented May 11, 2014 at 2:34

EthanK · Accepted Answer · 2014-05-11 02:53:24Z

3

function vlookup($lookupValue,$array){

    $result;

    //test each set against the $lookupValue variable,
    //and set/reset the $result value

    foreach($array as $key => $value)
    {
        if($lookupValue > $key)
        {
        $result = $value;
        }
    }

    return $result;

}

edited May 11, 2014 at 2:53

answered May 11, 2014 at 2:31

EthanK

7787 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EthanK Over a year ago

you did say "I need a function" right? 'cus this is the best I can think of, there's nothing native like that in PHP

Kelvin Over a year ago

What if the key value is not an integer? for example 44.8 => 7

pnbps · Accepted Answer · 2017-06-24 04:43:13Z

3

function testDeductionRate(){echo $this->deductionRate('ks',300);//zone and time are parameter for deductionRate() }

//deduction will be acted as vlookup (rangeLookup true)
function deductionRate($zone,$time){
$actualRate = 0;
$allRate = array(
    'tg'=>array(0=>0,20=>200,30=>300,40=>400,50=>500), 
    'ks'=>array(0=>0,20=>100,30=>200,40=>300,50=>400), 
    'pc'=>array(0=>0,20=>50,30=>100,40=>200,50=>300) 
);
if(isset($allRate[$zone])){
    $rate = $allRate[$zone];
    foreach($rate as $key=>$val){
        if($time>=$key) $actualRate = $val; 
    }
}else{
    return -1; //-1 means error
}
return $actualRate;


}

edited Jun 24, 2017 at 4:43

answered Jun 24, 2017 at 4:37

pnbps

672 silver badges6 bronze badges

Comments

SapuSeven · Accepted Answer · 2018-08-23 10:51:16Z

1

Try this if you would like to query on multi dimension associative array:

 function vlookup($lookup_vakue, $lookup_array, $lookup_column, $result_column)
    {
        foreach ($lookup_array as $item) {
                if ($item[$lookup_column] == $lookup_vakue) {
                    return $item[$result_column];
                }
        }
        return false;
    }

Sample Data

$data = 
[ 
   ['id'=>1,'price'=>100],
   ['id'=>2,'price'=>200],
   ['id'=>3,'price'=>300]
];

Query Option

$result = vlookup('2',$data, 'id','price');

Result:

edited Aug 23, 2018 at 10:51

SapuSeven

1,59118 silver badges31 bronze badges

answered Aug 23, 2018 at 10:06

Bhim Bhandari

112 bronze badges

Comments

NicolasV · Accepted Answer · 2019-08-25 18:43:41Z

0

$getbase= function($amount) use ($baseCalculationPrice)
    {
        return (end(array_filter(
        $baseCalculationPrice,function ($key) use ($amount) 
            {
              return $key < $amount;
            },
        ARRAY_FILTER_USE_KEY
        )));
     };

amount 150 result 50 amount 1500 result 18 amount 25000 result 14

answered Aug 25, 2019 at 18:43

NicolasV

52 silver badges4 bronze badges

Collectives™ on Stack Overflow

PHP equivalent of Excel vlookup on array

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related