Excel - find value in row which is not an error or blank

1
votes

Someone posted a question about doing this for a column but I don't understand the answer well enough to tweak it to work for data in a row. Can someone help me understand how this formula works and how to apply it to data in rows? I want to display the non error value in a new column, not the number of the row or column.

Thanks!

Try this formula

=LOOKUP(2,1/(A1:A100<>""),A1:A100)

extend range as required. In Excel 2007 and later you can use the whole column

Original question here: Excel - find last value in a column which is not an error or blank

1
arraysexcellookup
Usually VLOOKUP or HLOOKUP is better, but in this particular case they're taking advantage of a difference in the older LOOKUP's reaction to not finding the search value. LOOKUP will return the last non-matching value, while HLOOKUP will return #N/A. – Denise Skidmore

1 Answers

2
votes
=LOOKUP(2, 1/(A1:G1<>""),A1:G1)

Just change the range in both places.