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

Return which cell has a number

Busymanjohn

Busymanjohn

Member
Hi guys, Poser for you, I have a range of data cells(BB5:BM5), so a total of 12 cells, only one of the cells in that range contains a number which is >=4, how do I return in another cell (BN5),which number from those cells contains a number >=4 ,,,, so e.g. in the range BB5:BM5, BK5 contains the number 4, in BN5 I want the result to say 10 as it's the 10th cell in that range .. make sense??
 
Hi ,


Try : =MATCH(TRUE,Data_Range>=4,0) entered as an array formula , using CTRL SHIFT ENTER ,


where Data_Range is BB5:BM5


Narayan
 
Hi,


Enter this formula in BN5:

SUMPRODUCT(((BB5:BM5)>=4)*COLUMN(BB5:BM5))-53


Hope it works,


@Narrayan: Nice formula man!


Regards,

Faseeh
 
Hi Narrayan, your formula returns the value 1 ,,,, needs to be 10, which shows me that the value within the range that contains the number 4 or greater is in the 10th column of the 12 columns in the range.


Faseeh, your formula returns a value of 661.


Any more ideas that I could try?
 
Hi ,


Anything that I post is tried by me , and only when I get the result that you are looking for , do I post.


Narayan


P.S. : This is not to say that I cannot make mistakes. In this case , I haven't. I copied the formula given : =MATCH(TRUE,BB5:BM5>=4,0) in an unused cell , and entered the value 7 in cell BK5 ; I got 10 as the result. Note that the formula has to be entered using CTRL SHIFT ENTER.


If this is not what you were looking for , then I have made a mistake. Please let me know.
 
Hi,


Have u entered my formula in BN5?? Bcz i first tried it on sheet and then posted. Even Narrayan's formula is giving correct result? In which cell do u have entered formula?


Thanks,

Faseeh
 
hey guys, I trued both your formulas in a blank sheet and yes they both work, the cells in my workbook that I am referencing ( BB5:BM5) contain formula, so I need to back and check that those are running correctly ,,,,, will keep you posted, and thanks for the solutions, much appreciated.
 
You must log in or register to reply here.
Back
Top