Lookup with tolerance

[seacrest]

Hello, I have a column with measurements in mm eg 4000 these represent timber lengths
I also have a column of available lengths on a separate sheet. eg 4200
I would like to lookup or match a length that can be used ie 4000 can be cut from 4200

So basically I am looking for a method to search with a tolerance of say 300mm so that the maximum waist would be no more then 300mm

 

[palaeontology]

if the waste is going to be more than the 300m, how do you want to proceed.

Can you please provide examples.

 

[seacrest]

if the waste is going to be more than the 300m, how do you want to proceed.

Can you please provide examples.

needs to work like a vlookup if it doesn't find a match then blank

 

[palaeontology]

ok, let's say you have your timber lengths in range E4:E29

and your available lengths in M4:M11 ... this list must be sorted in ascending order

then, let's say, in G4, enter ... =IF(ISERROR(INDEX($E$4:$M$29,MATCH(E4,$M$4:$M$11,-1),9)),"",IF(INDEX($E$4:$M$29,MATCH(E4,$M$4:$M$11,-1),9)-E4>300,"",INDEX($E$4:$M$29,MATCH(E4,$M$4:$M$11,-1),9)))

let me know if that is not what you're looking for

Kind regards,

Chris

 

[palaeontology]

oops, sorry, I meant to say the list in M4:M11 should be sorted in Descending order !!!!!!

Chris