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

INDEX SMALL with >

Busymanjohn

Busymanjohn

Member
Hi all, I am having some trouble with getting the required results from a multiple VLOOKUP ( INDEX, SMALL ) when I input a > sign rather than an = sign ..... i.e. my formula is INDEX(page!$B$1:$U$2671,SMALL(IF(page!$B$1:$B$2671=3,ROW(page!$B$1:$B$2671)),ROW(page!1:1))*1,2)

page is the sheet name that contains my data, full range of the data is in cols B to U, with col B containing the start point ( number of days in this case ) ... formula works fine with an = sign after the IF statement (IF(page!$B$1:$B$2671=3, but when I change the = sign to a > sign the result is error or returns the value in the cell above .... is there something significant about using a > sign in this formula?
 
John
Two things to try ,
1. Hit Shift+Ctrl+ Enter in formula bar. then you see formula will {...} .
2. try to put =>.

upload sample sheet for faster response.

-Ashwin
 
Sample file attached ,,,, Ashu, I know about the array ( should have mentioned that already ) ,,,, I have highlighted in the attached the data that should be returned, but one line is missing from the result.
 

Attachments

  • Test.xlsx
    10.2 KB · Views: 6
Hi John,

I think you just need a small amendment, highlighted in Red:

=IFERROR(INDEX($B$2:$E$11,SMALL(IF($B$2:$B$11>=4,ROW($B$2:$B$11)-1),ROW(1:1)),COLUMN(B1)),"")

CSE...

I have also replaced 2 with COLUMN(B1), column number will be updated when you drag to right. You can also use the MATCH function here.

Regards,
 
Khalid, that works fine ,,, now, if I had the formula in another sheet, then the COLUMN piece does not work, any ideas?
 
You must log in or register to reply here.
Back
Top