Random Sub Category From Category

[bemp87]

Hi Community - I'm hoping you can help:

I have two Columns:

On a data sheet titled 'DataSheet' i have the following:

Column A: Contains a list of product categories
Column B: Contains a list of sub-categories for each category

On my active Sheet titled 'BusinessSheet' I want to be able to create a formula that will return a random sub category in Column E for each category type listed in Column D.

I.e. Column D might have the following category values:
D1: Sports
D2: Computers
D3: Financial

Sports will have the following values, Golf, Basketball, Football as the subcategories. COmputers might have, Mac, Windows, Linux as the subcategories. And financial will have Loans, Credit Cards, Checking as the sub categories. I want to return a random subcategory with the matching category using a formula.

I hope this makes sense, I have tried numerous formula expressions and can't see to make it right. ANy help would be apprecaited.

 

[steve the fish]

This would work for your example:

=INDEX({"Golf","Basketball","Football";"Mac","Windows","Linux";"Loans","Credit Cards","Checking"},MATCH(D1,{"Sports","Computers","Financial"},0),RANDBETWEEN(1,3))

 

[bemp87]

Hi Steve,

Is there a way to do this without having to write it out literally. I was assuming this was one way to do it, but i have 360 subcategories across 42 different categories, which would be an extremely long expression, any other way to use the match with maybe a chose function or something for it to select one of the coresponding sub categories

 

[steve the fish]

If you place the catergories along the top row and the subcategeories underneath them then yes no doubt one could be written. The answer is it depends how its set up on the sheet.

 

[steve the fish]

So place 'Sports' in A1, 'Computers' in B1 and 'Financial' in C1. Place the category to get your random subcategory in E1. Place your various subcategories underneath your category headers. A2,A3,B2,B3,C2 etc. Then:

=INDEX($A:$C,RANDBETWEEN(2,MATCH("zzz",INDEX($A:$C,0,MATCH($E$1,$A$1:$C$1,0)))),MATCH($E$1,$A$1:$C$1,0))

 

[bemp87]

Hi steve,

This worked perfectly, thank you so much! I had something similar and close, but was just a little off, but this solves my problem. Thanks again!