I think the problem might be that you're using the LOOKUP function, which finds an exact answer that you are then trying to match in the list. To correct it, remove the LOOKUP function and leave the MATCH function by itself since the MATCH function finds the relative position in the list. This will take care of duplicates since a position cannot be a duplicate. Type this formula into C1 and instead of hitting Enter, hit Ctrl+Shift+Enter (Ctrl, Shift, Enter):=IF(MATCH(9.99E+307,$B$1:$B$12)=A1,"Hello","")
That will work fine, but you would still need to keep the numbers in the first column to match against.
The formula I prefer is this:
Instead of trying to match the relative position to the numbers in the first column, it uses an expanding range to count the row number you are in. Try this formula and it will work even if you delete all of the numbers in column A.
By the way, the formulas above give you the last number in the list. If you want the last word in a list, use this. It works in a list with words only and it also works in a list with words and numbers:
If your list contains mixed data and you want the last non-blank (i.e,, you want whatever the last value is regardless of whether it is a word or number), use this:
Remember to use Ctrl+Shift+Enter for all of these.