Lookup, compare for closest value and return value

[PGipe]

I am trying to look up a particular part number, compare to find the closest quantity and return the cost associated with that closest quantity.


Spreadsheet A
Part Number QTY Cost
123 18 $14.00
123 72 $9.21
123 144 $7.83

Spreadsheet B
Part Number QTY Cost
123 25 Would return $14.00
123 59 Would return $9.21

 

[NARAYANK991]

Hi ,

What would be the definition of the closest quantity ?

Suppose your data had been as follows :

   PartNumber QTY     Cost
      123      18    $14.00
      123      27    $ 9.77
      123      72    $ 9.21
      123     144    $ 7.83

In this case , for the same data on worksheet B , what would be the closest quantity ? Would it be 18 or 27 ?

Secondly , when you say spreadsheet , do you mean worksheet or workbook ?

Narayan

 

[PGipe]

Closest quantity would be based on the difference between the quantity in the lookup worksheet (in worksheet B it is 25) and the data worksheet or worksheet A. In the first example, it would compare 25 to the quantities and return value for quantity 27, which would be $9.77.

Also, these are 2 worksheets in the same workbook.

 

[Somendra Misra]

Hi,

See the file. Sheet2 has the solution.

Regards,

 

[prasaddn]

hi,

This is really very interesting question.

I am sure some mmult or array function can fix the problem.

I am not good at either of them , but I did try my best to get your answer with two helper columns. Again, I could not club the results of helper columns in main formula to make it without helper column.

Anyways here is the solution if you are okay using two helper columns.

Yes, I am looking forward to learn more to get the better solutions

Regards,
Prasad DN

 

[PGipe]

Thanks to Somendra for the help!! I'm trying to decipher some of the formula, but it appears to be working!!

 

[Somendra Misra]

Glad if it helped you to solve the issue. You are Welcome here again!!!

Regards,