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]

Blatant bump but is anyone able to help with this please??

 

[Weazel]

just a thought but are you sure column U is sorted ascending ?

 

[Jonmo1]

Just a clarification on these functions (building on Weazel's question).

Vlookup (with TRUE as the 4th argument) and MATCH with 1 as the matchtype.
These functions DO NOT actually find the 'closest match'

What they actually do is find the Largest value that is Less Than or Equal To the Lookup Value.
And they require/assume the values in the lookup array to be sorted in Ascending Order.

So if your lookup range had say
10 15 30
And your lookup value was 28, it would return 15. Dispite 30 actually being the 'closest match'.


So questions:
Is the data sorted in ascending order in U3:U368 ?
If not, CAN it be?
Are you actually trying to find the real 'closest match', whether it's greater or less than lookup value?
If so, another completely different approach will be required.

 

[KatieWales]

[QUOTE


So questions:
Is the data sorted in ascending order in U3:U368 ?
If not, CAN it be?
Are you actually trying to find the real 'closest match', whether it's greater or less than lookup value?
If so, another completely different approach will be required.[/QUOTE]

Hi both

Thanks for the clarifications!! And sorry for the delayed reply. The data is not sorted in ascending order in U3:U368 and it can't be as other calculations later depend on the trial structure remaining constant. Yes I am looking for the real closest match to the lookup value

Thanks in advance

Katie

 

[Mackers]

Hi Katie

You might want to try something like this, using control+shift+enter:

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

This creates an array that is the absolute difference between each value in U3:U368 and N11, then finds the position of the minimum value (i.e. the value in U3:U368 that is closest to N11).

Hope that helps

Mackers

 

[KatieWales]

Hi Mackers!

That works! Thank you very much However, just playing around with the formula on other participant's data I noticed that when some cells within u3:u368 contain #N/A or #DIV/O! (due to data drop off) the formula returns an error. Is there a way of making the formula ignore these cells please? Thanks!!

Katie

 

[AliGW]

=iferror(index(v3:v368,match(min(abs(u3:u368-n11)),abs(u3:u368-n11),0)),"")

 

[KatieWales]

=iferror(index(v3:v368,match(min(abs(u3:u368-n11)),abs(u3:u368-n11),0)),"")

Thank you AliGW!! And thanks again Mackers! This has been a massive help

 

[Mackers]

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