Loop formula to search through range to get expected output

**raj2989** · 02-14-2014, 09:16 AM

Trying to look for formula for searching through Range and search through Data to achieve expected res

Data Output Expected Range
Pune 123 is city Pune 123 Mumbai 999
Maharashtra 345 is state Maharashtra 345 Pune 123
India 678 is Country India 678 Nagpur 666
Maharashtra 666 is state Others Pune 555

Below is the query i used for manual search however need formula to see through Range and achieve output expected

=IF(ISNUMBER(SEARCH("Pune 123",A5)),C5,
IF(ISNUMBER(SEARCH("Maharashtra 345",A6)),C6,
IF(ISNUMBER(SEARCH("India 678",A7)),C7,"Others")))

**azumi** · 02-14-2014, 09:24 AM

You have the sample workbook? maybe better if you try to upload it at the forum

**raj2989** · 02-14-2014, 09:50 AM

I want to take input from Range column and match it with Data Column and if match found put "results" else "Others"

i hope you can understand the requirement

**azumi** · 02-14-2014, 10:34 AM

Please check the fles, something like this....

=IF(ISERROR(LOOKUP(2^15;SEARCH(TRIM(LEFT(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)));$C$2:$C$7);$C$2:$C$7));"Others";LOOKUP(2^15;SEARCH(TRIM(LEFT(A2;SEARCH(" ";A2;SEARCH(" ";A2;1)+1)));$C$2:$C$7);$C$2:$C$7))

**raj2989** · 02-17-2014, 07:36 AM

Azumi

thanks it worked like charm.....

One more question how can this query be made ? to search the data in entire text field... its resulting in "Others" when there is some text at start... see the attached example

Appreciate your help on this

**azumi** · 02-17-2014, 09:15 AM

Not clear about the result that you've needed to achieve, maybe describe it with before and after situation....

Loop formula to search through range to get expected output

LinkBack

Thread Tools

Rate This Thread

Display

Loop formula to search through range to get expected output

Re: Loop formula to search through range to get expected output

Re: Loop formula to search through range to get expected output

Re: Loop formula to search through range to get expected output

Re: Loop formula to search through range to get expected output

Re: Loop formula to search through range to get expected output

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Expected Do before loop (compile error)

Formula to search column for match and output date in corresponding row

Loop to find a criteria within range, then output results to individual lines

Excel 2007 : Excel 2007 Reverse the Expected Output

[SOLVED] Search range of cells, find a value, output adjoining cell. How?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions