Matrix formula INDEX and MATCH lookup values between x and y

[TimvMechelen]

Hi all,

I have a list of data (songs), numbered in column A starting at cell A3 with 1. In column E I have the BPM (Beats Per Minute) starting at cell E3.
I want to search for all songs with matching BPM within Excel, so the output will be a list of the numbers of the songs with matching BPM.
For example: i have a song with BPM 128 (in cell N6), to search for matching songs i used the matrix formula (dutch):
=ALS.FOUT(INDEX(Tracks!$A$3:$A$803;VERGELIJKEN(0;AANTAL.ALS(N$6:N6;ALS(Tracks!$E$3:$E$803=$N$6;Tracks!$A$3:$A$803;$N$6));0));"")
(TRANSLATIONS: als.fout = IFERROR , Vergelijken = MATCH , aantal.als = COUNTIF , als = IF)

This works and the output is a list of all songs with the exact BPM of 128.
But there are songs with a BPM of 129 in the list for example, or 128.50. So what I want now is a list of tracknumbers that have a BPM within a range of 10% of the song that I want to find matches for (i.e. find songs with a BPM between 115.2 and 140.8, because this is 10% above 128 and below 128)

Is it possible to do this with a formula?

Thank you for your time.

 

[Aladin Akyurek]

=IFERROR(INDEX(Tracks!$A$3:$A$803,SMALL(IF(Tracks!$E$3:$E$803>=crit1,IF(Tracks!$E$3:$E$803<=crit2,ROW(Tracks!$A$3:$A$803)-ROW(INDEX(Tracks!$A$3:$A$803,1,1))+1)),ROWS($1:1))),"")

If this doesn't make sense, try to post a small sample (input) along with the output which must obtain from the input.

 

[TimvMechelen]

thanks it works!

An additional question:
What if i only want songs with a bpm of 127, 128 and 129? What formula should I use then?

 

[Aladin Akyurek]

thanks it works!

An additional question:
What if i only want songs with a bpm of 127, 128 and 129? What formula should I use then?

Control+shift+enter, not just enter, and copy down:


=IFERROR(INDEX(Tracks!$A$3:$A$803,SMALL(IF(ISNUMBER(MATCH(Tracks!$E$3:$E$803,bpmcritlist,0)),ROW(Tracks!$A$3:$A$803)-ROW(INDEX(Tracks!$A$3:$A$803,1,1))+1),ROWS($1:1))),"")

bpmcritlist is a three-cell range which houses 127, 128, and 129.


If so desired, you can also:

=IFERROR(INDEX(Tracks!$A$3:$A$803,SMALL(IF(ISNUMBER(MATCH(Tracks!$E$3:$E$803,{127,128,129},0)),ROW(Tracks!$A$3:$A$803)-ROW(INDEX(Tracks!$A$3:$A$803,1,1))+1),ROWS($1:1))),"")