# How to find the last instance of data in a series of cells?

#### stuckagain22

##### Board Regular
I have a spreadsheet that consists of a data area which spans about 200 columns and 30 rows.

I want a column to show the last instance of cell value in that row of 200 cells.

i.e.

| 2 | 5 | 4 | 9 | 1 | _ | _ | 3 | _ | _ |

In the above example I want to show that "3" is the last value in the row.

I can't think of good way to do this. Any suggestions?

Last edited:

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
After a fairly lengthy Google search, I think I found a way to do this. (Actually 2 ways, but one uses Lookup and ROW references -- and I always avoid ROW references as I still don't know if Excel has ever fixed its internal error these used to cause).

This seems to be working:
=OFFSET(A1,0,MATCH(MAX(A1:GZ1)+1,A1:GZ1,1)-1)

Hi,

LOOKUP formula, no need for ROW function, And without the volatile OFFSET function.

Use L1 formula if the Values are Numbers,
Use L2 formula if the Values are Text,
Use L3 formula if the Values are Mixed:

Book1
ABCDEFGHIJKL
12549133
2a1b2c3d4e5f6f6
32b4d1oooo
Sheet613
Cell Formulas
RangeFormula
L1=LOOKUP(9.99999999999999E+307,A1:J1)
L2=LOOKUP("zzz",A2:J2)
L3=LOOKUP(2,1/(A3:J3<>""),A3:J3)

Last edited:
LOOKUP formula, no need for ROW function, And without the volatile OFFSET function.

Thanks a lot jtakw.

That is a much simpler Lookup than the ones I found. In my case it is just numbers that will be in the row. The numbers will never be higher than 365. Would I still need to use "9.99999999999999E" or could this be shortened? I'm also not sure what the significance of "+307" is, but is this just part of the "magic" that makes this formula work when combined with the above number?

If you're Absolutely Certain your numbers will Never be larger than 365, you can effectively use 366...or use the alternative below.
That number I used 9.999...E+307, often referred to as the "Big Num", is supposedly the largest number Excel can handle (although there's some debate on this).

=LOOKUP(366,A1:J1)
=LOOKUP(9^9,A1:J1)

Replies
5
Views
348
Replies
2
Views
177
Replies
10
Views
381
Replies
9
Views
363
Replies
16
Views
798

1,203,071
Messages
6,053,371
Members
444,658
Latest member
lhollingsworth

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

### Which adblocker are you using?

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

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