In the spreadsheet below I am using the values from the table and an inputted value (yellow box) inside the formula =VLOOKUP(B16,F15:G29,2,TRUE) to find the correct emulation batch.
B16 is the value i have entered (yellow box) and is the one I am using to look up. F15:G29 are the values in the box. 2 shows that I want the value in the 2nd column, and TRUE that it is an approximate match (FALSE would be an exact match). I have to use the approximate match because i am measuring mean particle size – so chances are very slim of getting one exactly to the same.
It works well except the result I get is always rounded down, as opposed to rounding to the closest value. Hence, for some values I get the wrong batch suggestion (as with the example values below). I would’ve thought because it is an approximate match that the closest value is used. Is there anyway around this? I have tried entering a ROUND in various places but this hasn’t helped, as of yet.
Thanks very much in advance.