• 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.

Using complex Array

M

Manish Bhatia

New Member
=IF(ISERROR(INDEX(EOD.xlsx!$A$1:$I$1648,SMALL(IF(AND(EOD.xlsx!$I$1:$I$1648>=40000,EOD.xlsx!$I$1:$I$1648<50000),ROW(EOD.xlsx!$I$1:$I$1648)),ROW(1:1)),1)),"",INDEX(EOD.xlsx!$A$1:$I$1648,SMALL(IF(AND(EOD.xlsx!$I$1:$I$1648>=40000,EOD.xlsx!$I$1:$I$1648<50000),ROW(EOD.xlsx!$I$1:$I$1648)),ROW(1:1)),1)).....Need help with this formula. If I use the above formula to check only one condition(>40000), it gives the desired result BUT with AND operator to check two logical values, the result is not coming... can anyone help please? Thanks in advance
 
Hi Manish ,

The way to use multiple conditions in an array IF statement is to nest them , not use the AND function ; try this array entered formula , using CTRL SHIFT ENTER :

=IF(ISERROR(INDEX(EOD.xlsx!$A$1:$I$1648,SMALL(IF(EOD.xlsx!$I$1:$I$1648>=40000,IF(EOD.xlsx!$I$1:$I$1648<50000,ROW(EOD.xlsx!$I$1:$I$1648))),ROW(1:1)),1)),"",INDEX(EOD.xlsx!$A$1:$I$1648,SMALL(IF(EOD.xlsx!$I$1:$I$1648>=40000,IF(EOD.xlsx!$I$1:$I$1648<50000,ROW(EOD.xlsx!$I$1:$I$1648))),ROW(1:1)),1))

Narayan
 
Hi Narayan,

Thanks for your reply. Yah, you are right about nested IF. I figured out and changed my formula and also had to change a reference cell. It worked. But I really appreciate your reply.
Now after finishing this, I moved onto adding something more to my sheet. Hey BTW, these sheets are only my personal sheets & not for any professional project or assignment.

Now let's say that the result of the above formula is "Manish", displayed in sheet 1 in column A1. And "Manish" is also there somewhere in Column A(A1:A1650) with more data in Sheet 2.

I want to click on "Manish" in Sheet 1 & it should send me to "Manish" in Sheet 2. So basically am trying to use hyperlink with Vlookup. It gives me error"Cannot Find the Specified File". I am not writing the Link location & making a silly mistake, I guess. Not successful yet. Any Suggestions?
 
here it is

=HYPERLINK(VLOOKUP(IF(ISERROR(INDEX(EOD.xlsx!$A$1:$I$1650,SMALL(IF(EOD.xlsx!$I$1:$I$1650>3000000,ROW(EOD.xlsx!$A$1:$A$1650)),ROW(1:1)),1)),"",INDEX(EOD.xlsx!$A$1:$I$1650,SMALL(IF(EOD.xlsx!$I$1:$I$1650>3000000,ROW(EOD.xlsx!$A$1:$A$1650)),ROW(1:1)),1)),EOD.xlsx!A1:A1650,1,FALSE))
 
Hi Manish ,

Try this array formula , entered using CTRL SHIFT ENTER :

=HYPERLINK(IF(ISERROR(INDEX(Sheet3!$A$1:$I$1650,SMALL(IF(Sheet3!$I$1:$I$1650>3000000,ROW(Sheet3!$A$1:$A$1650)),ROW(1:1)),1)),"","#Sheet3!" & ADDRESS(SMALL(IF(Sheet3!$I$1:$I$1650>3000000,ROW(Sheet3!$A$1:$A$1650)),ROW(1:1)),COLUMN(Sheet3!$A$1))),INDEX(Sheet3!$A$1:$I$1650,SMALL(IF(Sheet3!$I$1:$I$1650>3000000,ROW(Sheet3!$A$1:$A$1650)),ROW(1:1)),1))

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