Solved: Extract State names - excel function

[vishwakarma]

I have list of state in one column(E) and a list of companies names in another (column A). Is there any way by which I can extract the names of the states from this list of companies names.

I need an excel function and not a vba...

I use the following function but it is not working...

=LOOKUP(TRUE,ISNUMBER(SEARCH($J$2:$J$100,C2)),$J$2:$J$100)

can any one help me on this...


Thanks..

[p45cal]

Spreadsheet Formulas
CellFormula B2
{=INDEX($E$2:$E$52,MAX(NOT(ISERROR(FIND($E$2:$E$52,A2)))*ROW($E$2:$E$52)-ROW($E$2)+1))}

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Not by entering the {} !

and copy down. See attached.

If there are two names or more in a cell in column A it will only choose the lowest one from the list of states.
Quick and dirty, a bit flaky and inelegant but it should save you some time.
4913

[Bob Phillips]

Try this array formula

=INDEX(E:E,MIN(IF(ISNUMBER(FIND($E$2:$E$52,A2)),ROW($E$2:$E$52))))

[vishwakarma]

Thank you guys...

both the formulas are working like a charm...


Thanks again..