Find the non-blank cell in a range and display the value of the cell

[Kully Rai]

Hi,

This seems straight forward but i am struggling to find a formula for this. I have data in rows and not all of the cells have data in them. For each row, i want to look up the range (i.e. A4:F4) and return the value from the last non-blank cell.

Could anyone please help?

 

[Mike H..]

Hi,

This seems straight forward but i am struggling to find a formula for this. I have data in rows and not all of the cells have data in them. For each row, i want to look up the range (i.e. A4:F4) and return the value from the last non-blank cell.

Could anyone please help?

Hi,

Try this. Drag down as required.

=LOOKUP(2,1/(A4:F4<>""),A4:F4)

 

[Faseeh]

@Kully Rai,

Hi,

With your data in second row, from A2 to H2, use this with Ctrl Shift Enter

=INDEX(B2:H2,MAX(ISBLANK(B2:H2)*COLUMN(B2:H2)))

See this file.

 

[Kully Rai]

Thank you both for the formulas. The Index formula worked for most of the rows but some came back with error for some reason and i couldn't see why. The Lookup formula also worked - thank you!