Choose function

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

Khalil Handal

Hi,
=CHOOSE(3,F9,G9,H9,I9,J9) will give the the value of H9 no matter what value
is in it.
Is there a way (or another function) so as it give the first non-empty cell
in the range F9:J9?
another formula in a different cell to give the second non-empty cell in the
same range?
 
8
=MATCH(99999,B3:B23)
Look for any number larger than possible in the range. For text "zzzzzzz"
 
This, array-entered*, returns the value in the 1st** non-empty cell within
F9:J9:
=INDEX(F9:J9,SMALL(IF(F9:J9<>"",COLUMN(F9:J9)),1)-COLUMN(F9)+1)
*press CTRL+SHIFT+ENTER to confirm the formula
**ie the leftmost

And this returns the value in the 2nd*** non-empty cell within F9:J9:
=INDEX(F9:J9,SMALL(IF(F9:J9<>"",COLUMN(F9:J9)),2)-COLUMN(F9)+1)
***2nd leftmost

Just change the numbers: 1, 2 within the SMALL to suit the desired nth
non-empty cell (from the left)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
tHANKS A LOT, worked fine

Max said:
This, array-entered*, returns the value in the 1st** non-empty cell within
F9:J9:
=INDEX(F9:J9,SMALL(IF(F9:J9<>"",COLUMN(F9:J9)),1)-COLUMN(F9)+1)
*press CTRL+SHIFT+ENTER to confirm the formula
**ie the leftmost

And this returns the value in the 2nd*** non-empty cell within F9:J9:
=INDEX(F9:J9,SMALL(IF(F9:J9<>"",COLUMN(F9:J9)),2)-COLUMN(F9)+1)
***2nd leftmost

Just change the numbers: 1, 2 within the SMALL to suit the desired nth
non-empty cell (from the left)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
Click to expand...
 
Another option using a *non* array formula:


For *first* non-empty cell:
=INDEX(F9:J9,MATCH(TRUE,INDEX(F9:J9>0,),0))

AND, to return the subsequent populated cells, just add a value in between
the last 2 parens:

=INDEX(F9:J9,MATCH(TRUE,INDEX(F9:J9>0,),0)+1)
=INDEX(F9:J9,MATCH(TRUE,INDEX(F9:J9>0,),0)+2)
.... etc.
 
Hi,
Your formula works well for me.
I needed an adjustment so I added an if statement as below:
=IF(BD27="",INDEX(ED27:EP27,SMALL(IF(ED27:EP27<>"",COLUMN(ED27:EP27)),2)-COLUMN(ED27)+1),"")
When BD27 = "" and there is not any empty cell in the range the outcome is
#NUM!.
Is there a way to overcome this and have "" in this case??
 
Try using ISERROR to trap just the SMALL part of it, array-entered:
=IF(BD27="",IF(ISERROR(SMALL(IF(ED27:EP27<>"",COLUMN(ED27:EP27)),2)),"",INDEX(ED27:EP27,SMALL(IF(ED27:EP27<>"",COLUMN(ED27:EP27)),2)-COLUMN(ED27)+1)),"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Perfect. Thanks again


Max said:
Try using ISERROR to trap just the SMALL part of it, array-entered:
=IF(BD27="",IF(ISERROR(SMALL(IF(ED27:EP27<>"",COLUMN(ED27:EP27)),2)),"",INDEX(ED27:EP27,SMALL(IF(ED27:EP27<>"",COLUMN(ED27:EP27)),2)-COLUMN(ED27)+1)),"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
Click to expand...
 
Back
Top