how to find last time word appears in column

The formula:
=LOOKUP(1,0/(E1:E10000=A1),E3:E10002)
doesn't work. It has two problems

a) that dividend must be 1 not zero
b) "Exit Point" is only a part of the contents of the cell...

It should be:
{=LOOKUP(2,1/(FIND(A1,E1:E10000)),E3:E10002)}

Originally Posted by protonLeah

. . . =LOOKUP(1,0/(E1:E10000=A1),E3:E10002)
doesn't work. . . . It has two problems

1st point is wrong. See this linked workbook. Download it and try it in several different Excel versions. For that matter, convert it to XLS format and try it in Excel 2003 or previous. FWIW, works for me in Excel 2000 and Excel in Office 365. For at least 2 decades, Excel's LOOKUP function has ignored errors in its lookup range/array when trying to bracket the value sought.

2nd point may be apt, but should text searching be case-insensitive or not? IOW, FIND or SEARCH?

ADDED: Note your recommended formula: =LOOKUP(2,1/(FIND(A1,E1:E10000)),E3:E10002)

What value would FIND return when A1 doesn't appear in the values of cells in E1:E10000? Wouldn't that value be #VALUE! ? If so, wouldn't the array 1/(FIND(...)) [FWIW, redundant parentheses] return #VALUE! errors for those entries? Why would you believe LOOKUP would happily ignore #VALUE! errors but choke on #DIV/0! errors?

=lookup(2,1/(find(a1,e1:e10000)),e3:e10002)

this works great I would just like the ”‚ not to be in the output

Originally Posted by davsth

. . . interesting debate. . . .

Not a debate. This is an elementary software engineering exercise since the behavior (formula results) is easily checked and verified.

. . . I have used LOOKUP(1,1/(E1:E10000=A1),E3:E10002) which appears to work . . .

I believe I've had problems when the max value of LOOKUP's 2nd argument appears multiple times and equals its 1st argument. The behavior being exploited is bracketing values for binary search, and that's safest when the 1st argument is guaranteed larger than any value in the 2nd argument.

. . . what is the difference between this and LOOKUP(2,1/(E1:E10000=A1),E3:E10002) or . . . LOOKUP(1,0/(E1:E10000=A1),E3:E10002) . . .

Perhaps nothing. No difference between these last 2 formulas because the numeric 1st argument is guaranteed to be greater than any numeric value in the 2nd argument. I haven't done exhaustive testing of LOOKUP's 1st arg possibly = multiple max values from 2nd arg, but why take the chance it doesn't work when 1st arg guaranteed > any 2nd arg values is sure to work?

Assuming:
1. We're looking for a numeric answer,
2. that there's the possibility of negative numbers,
3. the characters always appear before the number.

Formula:

Please Login or Register  to view this content.

or use LOOKUP(2,1/.... either works. Also, only OP knows if SEARCH or FIND is more appropriate for his situation.