• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Main category to be displayed when any subcategory is selected

J

Jagdev Singh

Active Member
Hi Experts

In the attached sample file you will find both main and sub categories listed. You will find the expected outcome in the sheet as well.

  • I will be adding both the main and subcategory in data validation

  • When anyone selects the subcategory (Column F) from the dropdown then the respective main category should be display in the adjacent cell (Column G) of it.
Please let me know if you have any doubts.

Regards,

JD
 

Attachments

  • category.xlsx
    11.5 KB · Views: 0
Please try this:

=LOOKUP(MATCH(D12,$D$12:$D$49,0),{0,5,12,16,28,35,50},$C$3:$C$8)

Where D12 contains the sub category
 
Hi Faseeh

Thanks for the above formula.

I will be adding data validation on Sub-category in column F. The current category and sub-category will give us an idea which sub-category belongs to which main category.

What I am looking for when a user selects any subcategory from the validation dropdown in column F then the respect main category should be displayed in column G.

Please let me know if I am unclear.

Regards,
JD
 
Last edited:
Jagdev,

I think Faseeh has provided what you asked for...His formula works for me -- with one change -- I am using F12 instead of D12 as the lookup value in the MATCH formula.

Paste the following formula in Cell G12 and drag down:

=LOOKUP(MATCH(F12,$D$12:$D$49,0),{0,5,12,16,28,35,50},$C$3:$C$8)

See attached.

Does your manual entry in cell G13 have an error? Perhaps it should be Incomplete Funds?

All best.
 

Attachments

  • jagdev1.xlsx
    12.1 KB · Views: 0
Hi Eibi and Faseeh

Thanks for the formula

Could anyone amongst you please help me understand why did we use "{0,5,12,16,28,35,50}" in the formula.

Regards,
JD
 
JD,

If you were to number the rows in your list of subcategories, you would find that the first blank row after SubCategory 1 is row 5, the first blank row after SubCategory 2 is row 12, the first blank row after SubCategory 3 is row 16, and so on {0,5,12,16,...}

In the formula that Faseeh provided, the work begins by finding the relative position of the selected SubCategory in the master list (using MATCH).

Then, the formula finds where that relative position fits within the list you asked about {0,5,12,16,...}, the "lookup vector"

For example -- in cell F13, you selected SubCategory "Incomplete Clearance"

The formula looks this string up in Range D12:D49, and finds it on the 15th row of the range. Next, the formula looks for the number 15 in the lookup vector:{0,5,12,16,...}, and in the absence of the number 15, it returns the position of the next smallest number: the number 12, which is the 3rd item in this array.

Finally, the LOOKUP function returns the corresponding (3rd) Main Category from list C3:C8 - the "result vector".

Consequently, if you add or subtract items from your list of SubCategories, you'll have to adjust the values in the lookup vector accordingly.

All best.
 
You must log in or register to reply here.
Back
Top