Can vlookup help in this situation?

Hi,
I'm trying to set a template on a set of data, in which there are categories and the sub-categories items, updated on weekly basis.

For the template, I need Excel to look for a specific category and display the entire row for all the sub-category items.

When trying with vlookup, it is only returning the first subcategory item and not all the subcategory items.

Kindly suggest if vlookup or any other simple and effective way to set the template.

Thanks

If you wanted a single formula, then you can use an INDEX/MATCH/SMALL combination, entered as an array formula. Alternatively, you could use a helper column with a simpler formula to identify all records which are subcategories and give each a unique identifier, then use an INDEX/MATCH formula to retrieve the data.

If you want an actual formula, it would help if you attached a sample Excel workbook, so we can see how your data is laid out and tailor the solution to you particular case.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Hi Pete_UK,
Thanks for your response.

Owning to the confidentiality clause, I cannot share the original data.
However, to understand the solution, I have attached an excel file with data on which the exact solution can be demonstrated.

Please find the attachment. In the attached file the data of countries is given on different parameters. I want to understand a way to find all the nations that fall under the Region, lets say OCEANIA, and paste the entire row of that region|country to the next sheet.

I hope this explains my situation. Please reply for any specific case related information!

Regards

In the attached file I have extracted a unique list of the regions and put this in Sheet3 (renamed as Ref_data), along with a formula to count the number of records for each region. I have also removed the extra spaces from the end of each region's entry.

I set up a data validation drop-down in cell B1 of Sheet2 (now called Extract), so that the region can easily be chosen - the number of records for that region is also shown in cell D1.

Then in Sheet1 (renamed as Raw_Data) I used column V as the helper column, with this formula in V2:

=IF(TRIM(A2)=Extract!$B$1,MAX(V$1:V1)+1,"-")

When this is copied down, it sets up a simple sequence for those records which match the chosen region. I have also coloured this column blue, as I tend to do with helper columns (for clarity). The formula has been copied down beyond your data (to row 250) to accommodate more data being added - the hyphens show where the formula is active.

In cell A4 of the Extract sheet I used this formula:

=IFERROR(INDEX(Raw_Data!B:B,MATCH(ROWS($1:1),Raw_Data!$V:$V,0)),"")

and this can be copied across and down as required - I've copied down to row 64 in the attached file, to ensure that all records are picked up.

Then you can just select another region from the drop-down in cell B1, and the display will automatically adjust.

Hope this helps.

Pete