Excel 2007 : How to Lookup in range & revert with list?

[ashu.j23]

A have a data record in a sheet contaning various information i.e. Stockist name, address, contact no, Dl No. company name (contaning 20 companies), if severals stockist have same compnies, how to find the list of stockist who have that companies

Name | Add | No. | DL No. | Comp. 1 | Comp 2 | Comp 3 | Comp 4 |
Abc 12 12 l45 A1 B1 C1 D1
Def 14 25 j785 D1 A1 H1 E1


Reqquired help....

[NBVC]

Assuming your table is in A1:H3 and the company names start in E2.

Make a list of unique company names, e.g in K2 down, list A1, B1, C1, D1, etc...

Then in L2 use formula:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($A$2:$A$3,SMALL(IF(ISNUMBER(SEARCH(" "&$K2&" "," "&$E$2:$H$3&" ")),ROW($A$2:$A$3)-ROW($A$2)+1),COLUMNS($K$1:K1)))))

this formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down the list and across as many columns as necessary to retrieve all names.

Note: Adjust ranges to match your database without going over by much... and re-confirm with CTRL+SHIFT+ENTER before copying down and across.