1) Please paste following values into A1:B11 cells in excel sheet.

1000     =IF(A1<1500,"NO","YES") 
1100     =IF(A2<1500,"NO","YES") 
1200     =IF(A3<1500,"NO","YES") 
1300     =IF(A4<1500,"NO","YES") 
1400     =IF(A5<1500,"NO","YES") 
1500     =IF(A6<1500,"NO","YES") 
1600     =IF(A7<1500,"NO","YES") 
1700     =IF(A8<1500,"NO","YES") 
1800     =IF(A9<1500,"NO","YES") 

2) You will see that the first cell which contains YES in B column is B6.

3) I am looking for any excel formula which gives me B6 without volatile.

Thanks in advance.

1) Please paste following values into A1:B11 cells in excel sheet.

1000     =IF(A1<1500,"NO","YES") 
1100     =IF(A2<1500,"NO","YES") 
1200     =IF(A3<1500,"NO","YES") 
1300     =IF(A4<1500,"NO","YES") 
1400     =IF(A5<1500,"NO","YES") 
1500     =IF(A6<1500,"NO","YES") 
1600     =IF(A7<1500,"NO","YES") 
1700     =IF(A8<1500,"NO","YES") 
1800     =IF(A9<1500,"NO","YES") 

2) You will see that the first cell which contains YES in B column is B6.

3) I am looking for any excel formula which gives me B6 without volatile.

Thanks in advance.

• excel
• excel-formula

Share Improve this question Follow edited Mar 22 at 14:41 Danny Coleiro asked Mar 22 at 14:34 Danny ColeiroDanny Coleiro 30322 silver badges1010 bronze badges 1

• =Match(true,A1:A9>=1500,0) entered with Ctrl+shift+enter in older Excel versions? – P.b Commented Mar 22 at 16:44

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 4

Please try this formula=> =ADDRESS(MATCH("YES", B:B, 0), 2) || It worked for me

MATCH("YES", B:B, 0) → Finds the row number of the first occurrence of YES in Column B

ADDRESS(row, column) → Returns the cell reference in the format B6

Cheers!