Lookup & Countif formula returning data in incorrect order

[gruntingmonkey]

Hello, I have a formula (as below)

F4 =LOOKUP(2, 1/((COUNTIF($F$3:F3, $C$18:$C$27)=0)*($C$18:$C$27<>"")), $C$18:$C$27)
F5 =LOOKUP(2, 1/((COUNTIF($F$3:F4, $C$18:$C$27)=0)*($C$18:$C$27<>"")), $C$18:$C$27)
F6 =LOOKUP(2, 1/((COUNTIF($F$3:F5, $C$18:$C$27)=0)*($C$18:$C$27<>"")), $C$18:$C$27)
etc...

Where column F is where the calculation is and column C is where a supplier name is.

When I add a new supplier in column C, it adds the new supplier to cell F4 if its not already been selected and moves all the other suppliers down one cell.

What I want is for the suppliers to be static and instead of going into cell F4, it shows in the next cell.

Any help would be great, ta.

 

[FormR]

Hi, if i've correctly understood - you can try this which is an array formula and requires committing with CTRL+SHIFT+ENTER

=INDEX($C$18:$C$27,MATCH(0,COUNTIF($F$3:F3, $C$18:$C$27)*($C$18:$C$27<>""),0))

 

[gruntingmonkey]

You Sir, are a legend. Thank you so much for that.