Retrieving information

Hi to all,

i have workbook in which I have two sheets, one of them cotains colums with information and in the other sheet I have to create a Search box to retrieve all from any Contact number, of course it has to be based on contact numbers culumn.

example:

CONTACT_NUMBER OPPORTUNITY_TYPE QUARTER CON_TYPE CONTRACT_TYPE_DESCRIPTION_LINE CURR_CODE
379159 OUTSOURCE 201002 C4P SMARTnet Onsite Premium 2x7x4 USD
389859 OUTSOURCE 201002 SNT SMARTnet 85xNBD USD
379159 OUTSOURCE 201002 C4P SMARTnet Osite Premium 4x7x4 USD
388859 OUTSOURCE 201002 SNT SMARTnet 85xNBD USD
388459 OUTSOURCE 201002 SNT SMARTnet 85xNBD USD
388459 OUTSOURCE 201002 SNT SMARTnet 85xNBD USD

and in the other sheet I have to create a search box template that based on the contact number it will retrieve all other information. Can any one helpme?

I use INDEX/MATCH for this, but you can also use VLOOKUP() or LOOKUP() based on some other restrictions about the layout of the table you're searching. INDEX/MATCH has none of those restrictions.

Here's some sample sheets showing exactly what you're looking to do.
INDEX-MATCH-Simple.xls

Try this:

Please Login or Register  to view this content.

Right-click on the Sheet2 tab and select VIEW CODE
Paste in the macro above
Press Alt-Q to close the editor and save your sheet.

Now type anything in cell A1 on sheet2 and it will retrieve all the data matching that number from Sheet1.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)