How to find first or last nonempty "valid" value in a range

TomCon

Active Member
Joined
Mar 31, 2011
Messages
272
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I want to find the first or last nonempty "valid" value in a range.

By "valid" value, i mean a numeric value, or a string.

So, in a range, if a cell is blank, has value "" [you can get it for instance with formula =IF(True,"","")], or has an error value, skip over it and keep looking.

So, for the following values {blank,ERR,"","red",55,blank,"",ERR}
Then
First nonempty = "red"
Last nonempty = 55

Anybody have a formula that will do this? (or could be separate formula for first value vs. last value). Or, at last resort, a VBA function that will return it. Getting either the value itself or the index would be ok.

Thanks much!
Tom
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You're not really using =IF(TRUE,"","") are you? ="" would do the same thing.

Are what you have marked as ERR error values or the actual text ERR?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,903
Office Version
  1. 365
Platform
  1. Windows
Not quite sure if this is what you meant, and I'm sure there is a better way to do this that I'm missing.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>#N/A</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Red</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">55</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>#N/A</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD>Red</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD style="TEXT-ALIGN: right">55</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A2</TD><TD>=NA()</TD></TR><TR><TD>A3</TD><TD>=""</TD></TR><TR><TD>A7</TD><TD>=""</TD></TR><TR><TD>A8</TD><TD>=NA()</TD></TR><TR><TD>B10</TD><TD>{=INDEX($A$1:$A$8,MIN(IF(LEN(IF(ISERROR($A$1:$A$8)+ISBLANK($A$1:$A$8),"",$A$1:$A$8)),ROW($A$1:$A$8))))}</TD></TR><TR><TD>B11</TD><TD>{=INDEX($A$1:$A$8,MAX(IF(LEN(IF(ISERROR($A$1:$A$8)+ISBLANK($A$1:$A$8),"",$A$1:$A$8)),ROW($A$1:$A$8))))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Excel Workbook
ABCD
1
2#DIV/0!Firstred
3redLast:55
455
5
6
7#DIV/0!
Sheet1
 

TomCon

Active Member
Joined
Mar 31, 2011
Messages
272
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

YES excellent...Great, BUT....

Great, thanks for showing me those array formulas. When i first looked at them, i thought "awesome, problem solved". But, it turns out that since the solution uses the row() function, which references a specific row in a sheet, it will only work on ranges that start in row 1. So, if my data was in, like, b10:b20, the formulas do not work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I did find the fix to make the solution more general. It is to subtract off one less than the first row in the range you are looking for the value in. After that change, the formulas will work on any range. So, the modified formulas look like this:<o:p></o:p>
<o:p></o:p>
<o:p>=INDEX(B21:B33,MAX(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33))))-ROW(B21:B33)+1)</o:p>
<o:p></o:p>
<o:p>=INDEX(B21:B33,MAX(IF(LEN(IF(ISERROR(B21:B33)+ISBLANK(B21:B33),"",B21:B33)),ROW(B21:B33)))-ROW(B21:B33)+1)</o:p>
<o:p></o:p>
<o:p>Thanks! This is great!</o:p>
<o:p>Tom</o:p>
<o:p></o:p>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Re: YES excellent...Great, BUT....

Great, thanks for showing me those array formulas. When i first looked at them, i thought "awesome, problem solved". But, it turns out that since the solution uses the row() function, which references a specific row in a sheet, it will only work on ranges that start in row 1. So, if my data was in, like, b10:b20, the formulas do not work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I did find the fix to make the solution more general. It is to subtract off one less than the first row in the range you are looking for the value in. After that change, the formulas will work on any range. So, the modified formulas look like this:<o:p></o:p>
<o:p></o:p>
<o:p>=INDEX(B21:B33,MAX(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33))))-ROW(B21:B33)+1)</o:p>
<o:p></o:p>
<o:p>=INDEX(B21:B33,MAX(IF(LEN(IF(ISERROR(B21:B33)+ISBLANK(B21:B33),"",B21:B33)),ROW(B21:B33)))-ROW(B21:B33)+1)</o:p>
<o:p></o:p>
<o:p>Thanks! This is great!</o:p>
<o:p>Tom</o:p>
<o:p></o:p>

For a definite range as B21:B33...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #b8cce4; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 width=64></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=middle>#DIV/0!</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>KAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>red</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=middle>#N/A</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=middle>#N/A</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>12</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19></TD></TR></TBODY></TABLE>

where B21 houses a formula like ="" in order
to simulate a formula blank, we can also invoke:

Control+shift+enter, not just enter...

=INDEX(B21:B33,MATCH(1,SEARCH("?*",B21:B33),0))

which should yield the first non-blank entry;

And just enter...

=LOOKUP(9.99999999999999E+307,SEARCH("?*",B21:B33),B21:B33)

which should yield last non-blank entry.

For any reference, we would either a List (Excel 2003) or a Table (Excel 2010 and later)
or construct a dynamic named range in order to run the foregoing formulas.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,903
Office Version
  1. 365
Platform
  1. Windows
Re: YES excellent...Great, BUT....

Great, thanks for showing me those array formulas. When i first looked at them, i thought "awesome, problem solved". But, it turns out that since the solution uses the row() function, which references a specific row in a sheet, it will only work on ranges that start in row 1. So, if my data was in, like, b10:b20, the formulas do not work.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I did find the fix to make the solution more general. It is to subtract off one less than the first row in the range you are looking for the value in. After that change, the formulas will work on any range. So, the modified formulas look like this:<o:p></o:p>
<o:p></o:p>
<o:p>=INDEX(B21:B33,MAX(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33))))-ROW(B21:B33)+1)</o:p>
<o:p></o:p>
<o:p>=INDEX(B21:B33,MAX(IF(LEN(IF(ISERROR(B21:B33)+ISBLANK(B21:B33),"",B21:B33)),ROW(B21:B33)))-ROW(B21:B33)+1)</o:p>
<o:p></o:p>
<o:p>Thanks! This is great!</o:p>
<o:p>Tom</o:p>
<o:p></o:p>

Aladins suggestion is far better, but for information, the trick here is omit the rows in the index array, using hotpepper's formula

<o:p>=INDEX(B:B,MIN(IF(NOT(ISERROR(B21:B33)),IF(B21:B33<>"",ROW(B21:B33)))))</o:p>
<o:p></o:p>
<o:p>The rest of the array restricts the result to rows 21:33, so an incorrect result can never be returned regardless of what is in B1:B20, or B34 onwards.</o:p>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top