last occurance of closest match

[jack_of_trades]

Hey guys, I need to find the last CLOSEST MATCH occurrence in a column of number values. The lookup value won't be an exact match so I need to find the last occurrence that is the closest to the lookup value. I found plenty of ways to find an exact match online but I am having a hard time finding it for 'closest match'. I am not savvy enough to find a way to incorporate the 'closest match' formula into the 'last occurrence' formula yet.

Ideally I would prefer the closest match to be <= rather than >= but I'll settle for anything at this point,lol.

I then need to have it return the row# that the last occurrence is in.

Thanks in advance,
Jamie

 

[Aladin Akyurek]

Well, here is a rough example of the way the numbers occur down the column:

RPM:
3500
3800
4120
4790
5500
5712
3610
3790
4680
5231
5427
5501
5680
5679
3548
3790
4175
4401
4900
5219
5410
5490
5530
5700

Lookup value = 5501

So ideally, the row# I am trying to lookup would be the italic value but I would settle for the value in bold to be returned too. Whichever is easiest at this point. Due to sensor output fluctuations and such, the RPM values are not always linear either, just throwing that out there in case someone needs that info for something on this.

Let A1:A25 house the sample you provided, the header included.

Let C1 house a RPM lookup value of interest like 5501.

C2, control+shift+enter, not just enter:

=MAX(IF($A$2:INDEX($A$2:$A$25,ROWS($A$2:$A$25)-1)-C$1<=0,IF($A$3:$A$25-C$1>0,ROW($A$3:$A$25))))

 

[Matty]

Hi Aladin,

I've had a look at the formula you've posted to try to get a handle on how you've tackled this problem. However, I'm getting a result of 14 using your formula rather than the expected 22 (based on a lookup of 5501).

I've tried to run the formula through the evaluator to work out, step by step, what's going on, but the evaluator doesn't like it and returns a #VALUE! error.

Could you help me to understand the logic of it, please?

Thanks,

Matty

 

[Aladin Akyurek]

Hi Aladin,

I've had a look at the formula you've posted to try to get a handle on how you've tackled this problem. However, I'm getting a result of 14 using your formula rather than the expected 22 (based on a lookup of 5501).

I've tried to run the formula through the evaluator to work out, step by step, what's going on, but the evaluator doesn't like it and returns a #VALUE! error.

Could you help me to understand the logic of it, please?

Thanks,

Matty

I get 24, the native row number of 5530, bolded by OP... By the way, the evaluator does not strand at all and returns 24 as it should.

 

[Matty]

OK, I've re-copied the data into Excel and entered your formula again, and I get 24 this time. But given a lookup value of 5501, isn't the closest match in row 23 (5490 - i.e. 11 away)?

By the way, the Evaluator still bombs out (I'm using Excel 2007). It's odd.

Matty

 

[Aladin Akyurek]

OK, I've re-copied the data into Excel and entered your formula again, and I get 24 this time. But given a lookup value of 5501, isn't the closest match in row 23 (5490 - i.e. 11 away)?

The bolded value is the latest time when RPM goes over the lookup value of 5501 from a value x that is less than equal to the lookup value. Experment a bit the sample: Try deleting last two records/rows.

By the way, the Evaluator still bombs out (I'm using Excel 2007). It's odd.
...

That's interesting. On 2010, it doesn't.

 

[jack_of_trades]

Let A1:A25 house the sample you provided, the header included.

Let C1 house a RPM lookup value of interest like 5501.

C2, control+shift+enter, not just enter:

=MAX(IF($A$2:INDEX($A$2:$A$25,ROWS($A$2:$A$25)-1)-C$1<=0,IF($A$3:$A$25-C$1>0,ROW($A$3:$A$25))))

This worked out great but I do have a question about it. The RPM column varies in range and isn't a constant (I usually just name the range from $A$2:$A$10000), Sometimes there are 150 rows, sometimes there are 10,000 rows depending on the file size and sample rate of the users software.

I just don't know how to modify your formula for a varying range? It will always have a header in Row1 and the data will always start in Row2 though. MAybe have it lookup the row # of the last row with data in it somehow?

Thanks do much for the help!!!!!!

 

[jack_of_trades]

So here is what I came up with:

=MAX(IF($A$2:INDEX([COLOR=Lime]Range[/COLOR],ROWS([COLOR=Lime]Range[/COLOR]))-$C$1<=0,IF([COLOR=Magenta]range2[/COLOR]-$C$1>0,ROW([COLOR=Magenta]range2[/COLOR]))))

then"CTRL+SHIFT+ENTER" to create an array.


Lookup Value= C1

Range=

=$A$[COLOR=Blue][B]2[/B][/COLOR]:INDIRECT("$A$"&(ROW(OFFSET($A$1,COUNTA($A:$A)-1,0))))

Range2=

=$A$[COLOR=Red][B]3[/B][/COLOR]:INDIRECT("$A$"&(ROW(OFFSET($A$1,COUNTA($A:$A)-1,0))))

This all seem correct? I am new to more complex coding,sorry.

 

[jack_of_trades]

Oops, I made a mistake, here is the correct formula (I think):

=MAX(IF($A$2:INDEX([COLOR=Black]Range,ROWS([/COLOR][COLOR=Black]Range[/COLOR])[COLOR=Red][B]-1)[/B][/COLOR]-$C$1<=0,IF[COLOR=Black]([/COLOR][COLOR=Black]range2-$C$1>0,ROW([/COLOR][COLOR=Black]range2))))[/COLOR]

then"CTRL+SHIFT+ENTER" to create an array.

 

[jack_of_trades]

If the lookup value is less than the lowest value in the range, it returns a zero. Shouldn't it find the find the closest match that is ABOVE the lookup value? There doesnt appear to be any false statement in the formula, is that why?

 

[Aladin Akyurek]

This worked out great but I do have a question about it. The RPM column varies in range and isn't a constant (I usually just name the range from $A$2:$A$10000), Sometimes there are 150 rows, sometimes there are 10,000 rows depending on the file size and sample rate of the users software.

I just don't know how to modify your formula for a varying range? It will always have a header in Row1 and the data will always start in Row2 though. MAybe have it lookup the row # of the last row with data in it somehow?

Thanks do much for the help!!!!!!

Let Sheet1 house the RPM data, with A1 containing the text bit RPM as header.

Define Size as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)-ROW(Sheet1!$A$2)+1

Define rpmALPHA as referring to:

=OFFSET(Sheet1!$A$2,0,0,Size-1)

Define rpmBETA as referring to:

=OFFSET(Sheet1!$A$3,0,0,Size-1)

Now invoke:

Control+shift+enter, not just enter...

=MAX(IF(rpmALPHA-C$1<=0,IF(rpmBETA-C$1>0,ROW(rpmBETA))))