Finding the last duplicate in a column and returning a text string.

Hi guys,

What I want to do is to find the last (not max) number from a range B1:B12 and when it is found return a string 'HELLO' in column C. In the picture the correct result (HELLO) would be displayed in a cell C12. I've got a formula so far but it doesn't go to the last value (see pic).

Info about the numbers in B1:B12 - they're produced by a formula and may contain duplicates; they're unsorted/random and the worksheet is set up to ignore zeros (checkbox is unchecked for show a zero in cells that have zero value).

Many thanks,

Stas

[Blocked Image: http://i62.tinypic.com/8zn3i8.png]

Re: Finding the last duplicate in a column and returning a text string.

Solution has been found from ExcelHelper84 from reddit.

Quote

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:
=IF(MATCH(9.99E+307,$B$1:$B$12)=ROWS($C$1:C1),"Hello","")
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(MATCH(REPT("z",255),$B$1:$B$12)=ROWS($C$1:C1),"Hello","
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:
=IF(MATCH(2,1/($B$1:$B$12<>""))=ROWS($C$1:C1),"Hello","")
Remember to use Ctrl+Shift+Enter for all of these.

Display More