# Drop Down List Multiple Outcome Formula

1. ## Drop Down List Multiple Outcome Formula

Hello,

I have 3 drop down lists on my "Summary" page. Each list is titled Drop Down List 1, 2, & 3.

Drop Down List 1
Cell A5 = Drop Down list of basketball team names.
Cell B5 = Which question (category) I'm trying to answer
Cell C5 = The answer to the options chosen in B5

Example: If i chose Alabama for Cell A5 & "Tourney App." for cell B5 then the answer that should be displayed in Cell C5 is the number 15. That number comes from the Yellow cell (Cell B7) of the "Seed Stats" page. The "Seed Stats" page is the page that will be used to answer all three questions in the Drop Down List of cell B5 on the "Summary" page.

Say i switch cell B5 on "Summary" to Overall Win/Loss Record than the answer in cell C5 will be 11 - 4 (with the format of 11_-_4). That answer is derived from cells C7 & D7 of the "Seed Stats" page.

And if i switch cell B5 on the "Summary" to Overall Win % than the answer in cell C5 will be 73% which comes from cell E7 of the "Seed Stats" page. The process would repeat itself for the any team that is chose for cell A5 and any category chosen in cell B5 of the "Summary" page.

What formula do i use for cell C5 to get the appropriate answers?

*Instead of making this post extra long to explain the other 2 drop down lists I'll wait until i solve the first drop down list before i continue on. Thanks in Advance!

2. ## Re: Drop Down List Multiple Outcome Formula

Hi,

Just to get this started off, I added a dropdown list for the team name in A5, go to data validation and change it to 'List', set the source as...

='Seed Stats'!\$A\$4:\$A\$354

Then in A5 you can lookup the data with...

=INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),3)

If you want to make B5 a dropdown list as well to lookup from the column headers on the Seed Stats sheet, then the fact that those column headers are merged cells with multiple columns below them is not ideal for doing a table lookup. You may have to adjust the layout.

3. ## Re: Drop Down List Multiple Outcome Formula

I'm sorry, but that document you uploaded does not work correctly. It will not display every teams tourney app let alone the other categories.

Take for example: If you chose "Belmont" in A5, cell C5 is blank. I'm thinking the #3 in the formula should be a 2.

It's going to be difficult to adjust the format because it's specific for that page.

4. ## Re: Drop Down List Multiple Outcome Formula

I've gotten this so far:

=IF(B5="Tourney App.",INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),2),IF(B5="Overall Win/Loss Record",INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),3)&" - "&INDEX(Table19,MATCH(Summary!A5,Table19[Team],0),4)))

That provides me with the first two answers (Tourney App. & Overall Win/Loss Record), but i don't know how to add the last answer (Overall Win %).

5. ## Re: Drop Down List Multiple Outcome Formula

Yes sorry, I put the wrong column number for the index function, it should have been column 2 for what I was saying, not 3.

Basically this lookup problem is made overly complicated by the design layout, the problems are....

1. You are looking up from table headers that contain merged cells, and the actual data you want is in multiple columns below that in some cases.

2. Not all table header look ups exist on the same row.

3. The output values are 3 completely different data formats.

Have a look at what I have done to this now, I unmerged the cells for the table headers, you could put loads of spaces in front to get them to the middle for the purpose of the appearance. I added the 'Overll Wn %' column header in E2, and 'Tourney App.' in B2 on the Seed Stats sheet, so that all the drop down list lookup values exist on the same row. I made B2 on the Summary sheet into a dropdown list using your range E4-E6 on that sheet.

Then accounted for the different formats to return data in, by using this rather long formula in C5 on the summary sheet.
Formula:

6. ## Re: Drop Down List Multiple Outcome Formula

Possibly this should be wrapped in an IFEROR statement, like this....

=IFERROR(IF(\$B\$5=\$E\$4,INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0)),IF(\$B\$5=\$E\$5,""""&INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0))&" - "&OFFSET(INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0)),0,1)&"""",IF(\$B\$5=\$E\$6,ROUND(INDEX(Table19[[Team]:[Win %]],MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0))*100,0)&"%"))),"")

Still not perfect though but getting somewhere at least I think

7. ## Re: Drop Down List Multiple Outcome Formula

How do i remove the " " around the win/loss record answer? The section in the E:F is just an example of the answers that are found on the "Seed Stats" page. I can't make anything in cell C5 = that range.

8. ## Re: Drop Down List Multiple Outcome Formula

Take out the &"""" , """""&, so write it like this......

=IFERROR(IF(\$B\$5=\$E\$4,INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0)),IF(\$B\$5=\$E\$5,INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0))&" - "&OFFSET(INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0)),0,1),IF(\$B\$5=\$E\$6,ROUND(INDEX(Table19[[Team]:[Win %]],MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0))*100,0)&"%"))),"")

Also if you do decide to add spaces in front of text in the column headers to central the text above multiple columns, then it may be necessary to use the TRIM function as well to account for that.

9. ## Re: Drop Down List Multiple Outcome Formula

I will continue to help as much as I can, it's all about understanding exactly what you want really and how best to go about it.

Do you understand what I mean about the table lookup? Ideally there needs to be 1 row on the lookup sheet that has all the same things which exist in the dropdown list.

10. ## Re: Drop Down List Multiple Outcome Formula

I think I'm following correctly.

In this example does the lookup table = E4:E16 on the "Summary" page? If so can i put that table on a different page in this workbook rather than on the "Summary" page?

11. ## Re: Drop Down List Multiple Outcome Formula

Every time Summary or 'Seed Stats' appears in the formula, it refers to the cell or range defined just after it on that sheet, you can change that in the formula to work for a sheet with a different name, as long as the layout is the same, remember I have made some changes to the layout on this example to allow this to work.

Also....

The dropdown list in B5 on the summary sheet is a list of the data in E4-E6 on the same sheet. To make a dropdown list, there has to be a list of the stuff you want in it on a sheet somewhere within the workbook.

Ideally you make the dropdown lists from the actual column headers and row labels of the lookup table. But you can just make a list of them somewhere else and use that instead, which is what is done here, just make sure they are all exact matches of the real lookup values. I have used your example data in E4-E6 for that dropdown list.

I have only looked properly at the top example, not the rest, so E4:E6 so far, not E4:E16. There is still some tidying up to do here before I would do the rest of them.....

Question:

Regarding how to display the data from range E:F, it is 2 separate values so one has to decide what we do with them. I have done it as shown on your example, with both separate values displayed with spaces and a dash between them, is this what you want?

I would be interested to know how you would like this to look where 1 of the 2 columns contains a value, while the other does not (this is in your example). The result from my formula as it stands looks untidy where this is the case. We could display just the one value that is there, but it wouldn't be apparent from this which of the 2 columns it had come from (wins or losses). The value could be labeled W or L based on which column it came from for example.

12. ## Re: Drop Down List Multiple Outcome Formula

To make a dropdown list, there has to be a list of the stuff you want in it on a sheet somewhere within the workbook
Does the list have to be vertical or can it be horizontal? I have the lists in a vertical format on "Sheet 1"

I have done it as shown on your example, with both separate values displayed with spaces and a dash between them, is this what you want?
If you are referring to the "Overall W/L Record" answer such as 11 - 4 than yes; number _ - _ number is the correct format. Thank you

I would be interested to know how you would like this to look where 1 of the 2 columns contains a value, while the other does not (this is in your example)
Ideally i would like Cell C5 to remain blank until A5 and B5 contain values.

If A5 = a value & B5 = no value than C5 = blank
If A5 = no value & B5 = value than C5 = blank
If A5 = no value & B5 = no value than C5 = blank
C5 = value only when A5 & B5 have values

13. ## Re: Drop Down List Multiple Outcome Formula

Hello again, sorry for the slow reply....

1. The dropdown list can be a horizontal range of cells yes, that's why it's good for all the relevant table headers to be on the same row if you want to make a dropdown list that refers to the columns, rather than the rows.

2. Great

3. I think you mean if one of the dropdown list cells is empty or blank.....

In which case....

=IF(COUNTBLANK(A5:B5)>0,"",<The rest of the formula goes here>)

Except maybe put the IFFERROR statement as the outer most function, like this.....

=IFERROR(IF(COUNTBLANK(\$A\$5:\$B\$5)>0,"",IF(\$B\$5=\$E\$4,INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0)),IF(\$B\$5=\$E\$5,INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0))&" - "&OFFSET(INDEX(Table19,MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0)),0,1),IF(\$B\$5=\$E\$6,ROUND(INDEX(Table19[[Team]:[Win %]],MATCH(Summary!\$A\$5,Table19[Team],0),MATCH(Summary!\$B\$5,'Seed Stats'!\$A\$2:\$E\$2,0))*100,0)&"%")))),"")

14. ## Re: Drop Down List Multiple Outcome Formula

Excellent, that works!! Thank you. 1 list down and two to go.

15. ## Re: Drop Down List Multiple Outcome Formula

Dropdown list 2...

I made a list of the column headers on the summary sheet needed for the dropdown list in B10, this could be hidden on a background sheet or something if you want.

The dropdown list for A10 just uses the team name column on the Seed Stats sheet.

The dropdown list for C10 refers to the range E9:E11 just next to it.

And then the formula in D10.....

=IFERROR(IF(COUNTBLANK(\$A\$10:\$C\$10)>0,"",IF(\$C\$10=\$E\$9,OFFSET(INDEX(Table19,MATCH(Summary!\$A\$10,Table19[Team],0),MATCH(Summary!\$B\$10,'Seed Stats'!\$A\$2:\$CG\$2,0)),0,1),IF(\$C\$10=\$E\$10,OFFSET(INDEX(Table19,MATCH(Summary!\$A\$10,Table19[Team],0),MATCH(Summary!\$B\$10,'Seed Stats'!\$A\$2:\$CG\$2,0)),0,2)&" - "&OFFSET(INDEX(Table19,MATCH(Summary!\$A\$10,Table19[Team],0),MATCH(Summary!\$B\$10,'Seed Stats'!\$A\$2:\$CG\$2,0)),0,3),IF(\$C\$10=\$E\$11,ROUND(OFFSET(INDEX(Table19,MATCH(Summary!\$A\$10,Table19[Team],0),MATCH(Summary!\$B\$10,'Seed Stats'!\$A\$2:\$CG\$2,0)),0,4)*100,0)&"%")))),"")

16. ## Re: Drop Down List Multiple Outcome Formula

haaa!!! You're awesome!!!!!! That's perfect.

17. ## Re: Drop Down List Multiple Outcome Formula

Thank you, I was happy to help

Now for dropdown list 3....

You need to make a list of the column headers from the Conference Stats sheet, to use to make the drop down list for B15. Like I have done for dropdown list 2. I suggest you don't have them as merged cells.

And then the formula needs to refer to 'table 20' instead of 'table 19' (the same range on the other sheet), the match function for the team name must refer to that sheet also, and refer to row 15 on the Summary sheet instead of row 10.

Do you want me to do the last one for you too, or can you work it out from what I've done so far?

18. ## Re: Drop Down List Multiple Outcome Formula

if you could that would be helpful so i can reference the set up for future use.

19. ## Re: Drop Down List Multiple Outcome Formula

Sure no worries.....

I unmerged the column header cells on the Conference Stats sheet.

The dropdown list in A15 refers to column A on the Conference Stats sheet.

The dropdown list in B15 refers to the list I made for it in column L.

The dropdown list in C15 refers to the range E14:E16 next to it.

And then the formula in D15...

=IFERROR(IF(COUNTBLANK(\$A\$15:\$C\$15)>0,"",IF(\$C\$15=\$E\$14,INDEX(Table20,MATCH(Summary!\$A\$15,Table20[Team],0),MATCH(Summary!\$B\$15,'Conference Stats'!\$A\$2:\$DZ\$2,0)),IF(\$C\$15=\$E\$15,OFFSET(INDEX(Table20,MATCH(Summary!\$A\$15,Table20[Team],0),MATCH(Summary!\$B\$15,'Conference Stats'!\$A\$2:\$DZ\$2,0)),0,1)&" - "&OFFSET(INDEX(Table20,MATCH(Summary!\$A\$15,Table20[Team],0),MATCH(Summary!\$B\$15,'Conference Stats'!\$A\$2:\$DZ\$2,0)),0,2),IF(\$C\$15=\$E\$16,ROUND(OFFSET(INDEX(Table20,MATCH(Summary!\$A\$15,Table20[Team],0),MATCH(Summary!\$B\$15,'Conference Stats'!\$A\$2:\$DZ\$2,0)),0,3)*100,0)&"%")))),"")

20. ## Re: Drop Down List Multiple Outcome Formula

Excellent, thank you very much!!!!

22. ## Re: Drop Down List Multiple Outcome Formula

You are welcome

If there is anything else then ask away.

If we are done here then please mark the thread as solved.