• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can I Index with more than one parameter?

K

Kamesh63

New Member
Hi:
Little help is needed... I would like to index an array and generate a report and return the values between two dates. Can I index with more than one parameter like below... I am trying with If(AND(...)) but getting an error. $C$3 and $E$3 contain date range for which I want to generate a report.

INDEX(Sheet1!$A$13:$J$6005,SMALL(IF(AND(Sheet1!$H$13:$H$6005>=$C$3,Sheet1!$H$13:$H$6005<$E$3),ROW(Sheet1!$H$13:$H$6005)),ROW(1:1))-1,1))

Is there any other way or use any other formula. Any help is appreciated.
Thanks!!
 
The AND function will force a single output, which isn't what we want. I believe you need:
=IFERROR(INDEX(Sheet1!$A$13:$A$6005,(1/SMALL(IF((Sheet1!$H$13:$H$6005>=$C$3)*(Sheet1!$H$13:$H$6005<$E$3),ROW(Sheet1!$H$13:$H$6005)),ROW(A1)))^-1),"")

Remember to confirm this as an array, and then copy down as far as would be needed. Any "extra" formulas will display blank.
 
Hi ,

Try this :

=INDEX(Sheet1!$A$13:$A$6005,SMALL(IF(Sheet1!$H$13:$H$6005>=$C$3,IF(Sheet1!$H$13:$H$6005<$E$3,ROW(Sheet1!$H$13:$H$6005)-MIN(ROW(Sheet1!$H$13:$H$6005))+1)),ROWS($A$1:$A1)))

Enter this as an array formula , using CTRL SHIFT ENTER.

Narayan
 
Thanks Luke and Narayan for a lightning reply. I tried both. Luke - I am getting error with your suggestion, actually its drawing a blank. Narayan's worked like a magic. Though I would be keen on understanding both.
Luke - what does ^ mean? My data is actually offset to Row 13. When I changed ^ to -13 it worked but only for first value, rest of the array drew blanks.

Narayan - I tried a formula similar to yours, except for ...(Min(Row.....) where I was giving actual row offsets, but did not work.

Thanks again both of you guys. I have been struggling with this over a day now...
 
It's part of the error check, and is independent of where data is. The "^" symbol is math notation for "raised to the power of". Overall, the formula uses a structure of:
(1/n)^-1

Which says to divide 1 by a number, and then take reciprocal. For any number other than zero, this will result in n. However, if there are no records found, or all records have already been found, then n = 0, and dividing by 0 will throw an error. the IFERROR then traps it, and outputs the "".
 
Sorry Narayan! I am stuck again. I am only able to get first column values. I tried putting in column numbers, but it is generating errors. My table actually has 6 columns. For instance the below formula generated #Ref error not ablet o figure out... Sorry I am not able to upload due to huge file size

=INDEX(Sheet1!$A$13:$A$6005,SMALL(IF(Sheet1!$H$13:$H$6005>=$C$3,IF(Sheet1!$H$13:$H$6005<=$E$3,ROW(Sheet1!$H$13:$H$6005)-MIN(ROW(Sheet1!$H$13:$H$6005))+1)),ROWS($A$1:$A1)),2)
 
Hi ,

For the INDEX function to accept a third parameter , as the column index , the range has to be a multi-column range.

Try this :

=INDEX(Sheet1!$A$13:$J$6005,SMALL(IF(Sheet1!$H$13:$H$6005>=$C$3,IF(Sheet1!$H$13:$H$6005<=$E$3,ROW(Sheet1!$H$13:$H$6005)-MIN(ROW(Sheet1!$H$13:$H$6005))+1)),ROWS($A$1:$A1)),columnindex)

where columnindex can vary from 1 to 10.

Narayan
 
You must log in or register to reply here.
Back
Top