Matrix formula INDEX and MATCH lookup values between x and y

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
121
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
=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.
 
Upvote 0
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?
 
Upvote 0
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))),"")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top