# Matrix formula INDEX and MATCH lookup values between x and y

#### TimvMechelen

##### Board Regular
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?

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

##### MrExcel MVP
=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

#### TimvMechelen

##### Board Regular
thanks it works!

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

##### MrExcel MVP
thanks it works!

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))),"")

Replies
11
Views
107
Replies
2
Views
45
Replies
7
Views
46
Replies
3
Views
79
Replies
4
Views
53