try:

=INDEX(Sheet1!A2:D2,MAX((Sheet1!A2:D2<"")*COLUMN( Sheet1!A2:D2)))

Enter with Control + shift+enter

Copy down

"holyman" wrote:

On Sheet 1 have the following data
Column A Column B Column C Column D
NEWPORT (COMM) LIVERPOOL GATESHEAD (CAR)
NEWPORT (COMM)
NEWPORT (COMM)
GATESHEAD (CAR) NEWPORT (COMM)
NEWPORT (COMM)

On Sheet 2 have the following formula to return the latest dealer.
=IF(sheet1!$D$2=" ",sheet1!$C$2,IF(sheet1!$C$2="
",sheet1!$B$2,IF(sheet1!$B$2=" ",sheet1!$A$2))). But my formula is not
returning any values. Please help

Row 2 should return Gateshead (CAR)
Row 3, 4 and 5 should return Newport (Comm)