VLOOKUP and INDEX-MATCH not doing it - any suggestions?

[KatieWales]

Hi all

I'm hoping someone will be good enough to help me with the below problem

In cell N11 I have a particular value which represents a measure of physiological activity (for example, .013543). In Column U I have a series of values which represent the changing amount of this measure over the course of an experimental trial. The measure does not increase linearly, it has many peaks and troughs. Column V contains the time in ms at which each measure of activity in Column U occurred over the course of this trial. I need to find the value in U which approximately matches the value in N11 and return the time in V that this value was matched at. The value in N11 represents 75% of a total level of activity, and basically I am trying to find the time point when 75% recovery has been reached.

I have tried VLookUp: =VLOOKUP(N11,U3:V368,2,TRUE) but this returns the time value before the first instance in U which is close to the target value of N11. I can easily just go to the cell below to find that value, but the formula isn't returning the closest value in U, just the closest value it comes to first.

So I tried INDEX-MATCH: =INDEX(V3:V368,MATCH(N11,U3:U368,1)) and while this works a little better, there are still values in Column U that are a closer match to the value in N11. For example the value that the INDEX-MATCH formula returns for .013543 is equivalent to 66% of the total recovery, which is too low. My parameters for accepting a value as a valid match from which to extract the time are 72.5% - 77.5% of the total level of activity. I've started to use a moving average of 3 cells (50 ms) in Column U in order to reduce the noise in the data, so ideally I would like a formula which returns the time of the best match within the above parameters - so far though this is beyond me so if anyone can help just find a decent match for 75% I would be so grateful!

Many thanks

Katie

 

[KatieWales]

Hi Katie

I think the formula you've been provided above will just return a blank if the index/match combination errors, it won't ignore the errors and return the closest cell, if that makes sense. You can adjust my formula to ignore errors (see below), but it might be better to adjust the formulas in the target data - you can wrap an IFERROR() around the formulas used in U3:U368, for example, so that if you get a DIV/0 or a #N/A it will just return a blank.

This is the adjusted version that excludes errors:

=INDEX(V3:V368,MATCH(MIN(IF(NOT(ISERROR(ABS(U3:U368-N11))),ABS(U3:U368-N11))),IF(NOT(ISERROR(ABS(U3:U368-N11))),ABS(U3:U368-N11)),0))

Hope that helps

Mackers

Hi Mackers

Thanks for this - I've wrapped the IFERROR around Column U as you suggested and it seems to be working fine

Thanks again so much for all your help

Katie