View Full Version : Solved: Extract State names - excel function
vishwakarma
11-16-2010, 05:08 AM
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
11-16-2010, 05:45 AM
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
11-16-2010, 05:50 AM
Try this array formula
=INDEX(E:E,MIN(IF(ISNUMBER(FIND($E$2:$E$52,A2)),ROW($E$2:$E$52))))
vishwakarma
11-16-2010, 06:15 AM
Thank you guys...
both the formulas are working like a charm...
Thanks again..
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.