Formula doesn't work when given range instead of column

I have formula which returns the last suppliers name, I want to start with that but as soon as I replace column A:A with a specific range, like A3:A36, it stops working.

so, the reason the 2nd doesn't work is that your INDEX range doesn't align to the ROW being returned by the SUMPRODUCT -- e.g

=INDEX(A:A,10) -> A10
=INDEX(A3:A36,10) -> A12 {the 10th element in the defined range}

so, given you're returning a ROW number in the SUMPRODUCT your INDEX should remain A:A, i.e.

=INDEX(A:A,SUMPRODUCT(MAX((A3:A36<>"")*ROW(A3:A36)))

that said, if your blanks are true blanks (and not null strings) you might find you can revert to the below, which would be much more efficient :

=LOOKUP(REPT("Z",255),A3:A36)

or, if per second screenshot you're looking to 'fill down' in Column B you can just use something like:

B3: =IF(A3<>"",A3,B2)
copied down

Hello dzumis. Welcome to the forum.

Somewhat similar to XLent's approach:
Given the screenshot setup and no clues how to identify the range limits in column A I propose 2 things.
1) a helper cell that specifies the upper limit of the range ... say B1
2) this "dynamic" named range in Name Manager Suppliers =Sheet1!$A$3:INDEX(Sheet1!$A:$A,Sheet1!$B$1)

Then use this formula in B3 and fill down until you get blanks.

Formula:

Please Login or Register  to view this content.

That will fill your output beyond the last Supplier1 as shown in screenshot.

Change the upper limit in B1 as needed. Until there is a way to automatically identify that upper limit by the data there remains the need for this manual input.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.