# match and give the value

1. ## match and give the value

i have attached an excel file , in that i want the names for the particular date where value corresponding to the particular date is greater than 4.

For examples , i should get names :x,q,s for date 22/02/16  Register To Reply

2. ## Re: match and give the value

Hi Gaurav
In A13 type date 22/02/16 in B13 type 4 then in A14 copy paste below then hold control and shift together and then hit enter to make it array formula
Formula:  `Please Login or Register  to view this content.`
drag down

array formulas are ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.  Register To Reply

3. ## Re: match and give the value

CAN YOU PLEASE EXPLAIN
how in this formula, second last ROW function and last ROWS function is working
means how it is executing steps.
actually i have to do that in large data , i should know how this happens  Register To Reply

4. ## Re: match and give the value

Would this work for you?
Enter where you want the results. I copied the dates into another area and entered this formula under the first date and filled across and down to give the names that had values >4 for each date.
Enter this ARRAY formula (Ctrl + Shift + Enter)
Formula:  `Please Login or Register  to view this content.`

The absolute reference to the range in column A assures that the reference to column A remains constant when the formula is filled across and down.
INDEX is the range that contains the values to be returned by the rest of the formula.
SMALL has IF to define the range of values to be considered. The IF statement IF(B\$3:B\$10>4 will only consider values in B3:B10 that are greater than 4. The TRUE part of the IF statement ROW(\$B\$3:\$B\$10)-MIN(ROW(\$B\$3:\$B\$10))+1 gives the values {1;2;3;4;5;6;7;8} which are the rows being evaluated for being greater than 4. When taken as a complete statement IF(B\$3:B\$10>4,ROW(\$B\$3:\$B\$10)-MIN(ROW(\$B\$3:\$B\$10))+1) is understood by the SMALL function that the IF is in as {FALSE;FALSE;3;FALSE;FALSE;6;7;FALSE}. All the FALSEs are the rows that have values that are <4. The numbers are the rows that have values >4.

ROWS(\$1:1) is the K factor for the SMALL function and will increase by 1 for each row filled down. This means that the smallest numbered row with a value >4 will be returned, the second row will have the next smallest row value containing a value >4 etc.

The whole formula is surrounded by and IFERROR which eliminates showing error values where there isn't data to be returned.

 I J K L M N 2 22/02/2016 23/02/2016 24/02/2016 25/02/2016 26/02/2016 27/02/2016 3 x z x z v z 4 q x d x d x 5 s d q v q s 6 7  Register To Reply

5. ## Re: match and give the value

@ gaurav
=IFERROR(INDEX(\$A\$3:\$A\$10,SMALL(IF(INDEX(\$B\$3:\$G\$10,0,MATCH(\$A\$13,\$B\$2:\$G\$2,0))>\$B\$13,ROW(\$A\$3:\$A\$10)-ROW(\$A\$3)+1),ROWS(\$A\$1:A1))),"")

Bold part is first construction that brings all numbers in form of array for matched date, then those array of numbers are checked whether greater than number available in B13 or not, if greater then for that number, Row number is returned and those which are not False is returned.

here the construction Row(\$A\$3:\$A\$10)-Row(\$A\$3)+1 gives the serial numbers from if function as below
{FALSE;FALSE;3;FALSE;FALSE;6;7;FALSE} as for attached file these are the row numbers where criteria is matched

Now small function picks 1 st smallest value from array and gives 3 which becomes row number for index function

Rows(\$A\$1:A1) generates serial numbers as 1,2,3,4,5,6 and so on as you drag your formula down that becomes nth factor for small function

For further insight click the cell with formula, go to formula tab and hit formula evaluation to check how things are taking place.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1