Match range of integer in another range

**moh87** · 10-14-2011, 11:26 AM

Dear Professionals;

I would first show my appreciation to your forum because I learned a lot through searching your forum.

I am facing a minor problem with Match formula. I would like to match any integer e.g (from 1-10) to a range let us assume (E11:Z11).

I have the following function:

=INDEX('Daily Productivity sheet'!$E$9:$Z$9,SMALL(MATCH(Any Integer,INDEX('Daily Productivity sheet'!$E$11:$Z$37,MATCH(O299,'Daily Productivity sheet'!$C$11:$C$37,0),0),0),2))

The range that I am searching in is: {0,0,0,0,0,8,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0}. (Assumed)

IF I used an Array function and I used {2,8} [Replaced Above Text Highlighted With Red]. it will works but not all the time I will have {2,8}. Sometimes I will have {1,2,3,4,5}

=INDEX('Daily Productivity sheet'!$E$9:$Z$9,SMALL(MATCH({2,8},INDEX('Daily Productivity sheet'!$E$11:$Z$37,MATCH(O299,'Daily Productivity sheet'!$C$11:$C$37,0),0),0),2))

I only need the function to match any integer (e.g 1:10) in a range since the Small function will help me to search in successive manner by lowest to second lowest and so on...

Pl. let me knew if it is applicable.

**daddylonglegs** · 10-14-2011, 11:53 AM

If the range will contain just zeroes and integers then presumably it will be sufficient to match with anything >0? If so try this version

=INDEX('Daily Productivity sheet'!$E$9:$Z$9,SMALL(MATCH(TRUE,INDEX('Daily Productivity sheet'!$E$11:$Z$37,MATCH(O299,'Daily Productivity sheet'!$C$11:$C$37,0),0)>0,0),2))

**moh87** · 10-14-2011, 04:15 PM

Originally Posted by daddylonglegs

If the range will contain just zeroes and integers then presumably it will be sufficient to match with anything >0? If so try this version

=INDEX('Daily Productivity sheet'!$E$9:$Z$9,SMALL(MATCH(TRUE,INDEX('Daily Productivity sheet'!$E$11:$Z$37,MATCH(O299,'Daily Productivity sheet'!$C$11:$C$37,0),0)>0,0),2))

Thanks for your reply. However I tried the new formula but I got #N/A

Pl. advice if there is any other solution.

**daddylonglegs** · 10-14-2011, 06:05 PM

OK, sorry, I realise that won't work for you - try this version

=INDEX('Daily Productivity sheet'!$E$9:$Z$9,SMALL(IF(('Daily Productivity sheet'!$E$11:$Z$37>0)*('Daily Productivity sheet'!$C$11:$C$37=O299),COLUMN('Daily Productivity sheet'!$E$9:$Z$9)-COLUMN('Daily Productivity sheet'!$E$9)+1),1))

confirmed with CTRL+SHIFT+ENTER

obviously change the red 1 as required.....

**moh87** · 10-15-2011, 04:25 PM

Originally Posted by daddylonglegs

OK, sorry, I realise that won't work for you - try this version

=INDEX('Daily Productivity sheet'!$E$9:$Z$9,SMALL(IF(('Daily Productivity sheet'!$E$11:$Z$37>0)*('Daily Productivity sheet'!$C$11:$C$37=O299),COLUMN('Daily Productivity sheet'!$E$9:$Z$9)-COLUMN('Daily Productivity sheet'!$E$9)+1),1))

confirmed with CTRL+SHIFT+ENTER

obviously change the red 1 as required.....

Hi Pro, the difference in timing between my country and yours might not help me in replying immediately to yours.

I was trying to work with your function and again it is not working. There might be something wrong in my understanding to the above mentioned function.

It will be rude to ignore your effort with this function. Pl. see the attached file to simplify the things and reduce your time and mine.

http://www.mediafire.com/download.php?1m933aq92csx998

=INDEX('Daily Productivity sheet'!$E$9:$Z$9,MATCH(0,INDEX('Daily Productivity sheet'!$E$11:$Z$37,MATCH(O299,'Daily Productivity sheet'!$C$11:$C$37,0),0),-1))

I recognized that lookup value in match function can be zero and match type can be greater than by adding -1. The function works fine and gets the first matching number. However I want to know how I could reach second and third match, etc....

Pl. accept my appreciation to your replies.

**daddylonglegs** · 10-15-2011, 06:17 PM

Are you confirming the formula with CTRL+SHIFT+ENTER - you need to do that to make it work correctly. Paste formula in cell, then press F2 key to select formula, hold down CTRL and SHIFT keys and press ENTER, curly braces should appear around the formula in the formula bar.

When I do that I get 3.1.17 for your example using the last formula I suggested - then when I change the 1 to a 2 I get 3.2.5 - if I use 3 in the SMALL function I get an error. Those results are as expected because you only have 2 numbers in the grid, one below 3.1.17 and one below 3.2.5

To avoid errors wrap the formula in an IFERROR function