Data Validation List with multiple choices based on one cell (w/o VBA)

[marshalltj67]

Good Afternoon,

Please see the attached excel workbook for reference.

I am trying to create a data validation list (Sheet: Flight Calculator - B13) to select all airports in a State (Sheet: CONUS Travel Source) based on a cell outputting the assigned state (Sheet: Flight Calculator - B7). ALL the airports for each State are in the CONUS Travel Source sheet and I need the data validation list to output all airports in accordance with the assigned State.

I tried using IF statements but will run out character space within the data validation tool. I also tried using INDEX and MATCH function but could only get it output the first listed airport instead of list of all the airport. I also tried assigning each state a number/assigning each airport a number but no luck.

Thanks in advance!

[#2]

This is the current formula I have in the data validation list:
=INDEX('CONUS Travel Source'!$H$3:$H$385,MATCH($B$7,'CONUS Travel Source'!$B$3:$B$385,0))
Do I need to apply a ROWS() somewhere so the formula can MATCH to a certain number of rows in accordance with the assigned State?

Thanks!

[#3]

What version of Excel do you have? Is it MS365 by chance?

[#4]

I am using a work computer (managed by admin) with Microsoft Office Professional Plus 2016 and don't have access to IFS statements.

[#5 · Accepted solution]

@marshalltj67 Give this formula a shot, entered directly in Data Validation as the Source:

=OFFSET('CONUS Travel Source'!$B$2, MATCH($B$7, 'CONUS Travel Source'!$B$3:$B$385, 0), 6, COUNTIF('CONUS Travel Source'!$B$3:$B$385, $B$7))

I tested it on my old laptop in Office 2010 just to be sure, and it seems to work just fine. Try it out and see if it's what you wanted.

[#6]

Worked perfectly!
Thank you so much!

[#7]

@marshalltj67 You bet! I don't know why I referenced cell B2 and offset 6 columns, lol. You can also just reference cell H2 (the column with the airport list) and offset 0 columns to get the same results:

=OFFSET('CONUS Travel Source'!$H$2, MATCH($B$7, 'CONUS Travel Source'!$B$3:$B$385, 0), 0, COUNTIF('CONUS Travel Source'!$B$3:$B$385, $B$7))

Cheers!

[#8]

Hello!

I am getting instances where my reference is off by multiple rows. Do you know how to fix this in the reference column?

[#9]

@marshalltj67 The key to this method working correctly is the setup. Since the MATCH function returns the row number where the first match is found in the lookup_array, and the COUNTIF function returns the total number of occurrences of the lookup_value in the lookup_array (which is passed to the [height] parameter of the OFFSET function), the lookup table MUST be arranged in order by the lookup_array column (ie: sorted by "State" in this case).

For example, if the lookup table lists 7 airports for "Arizona", followed by 4 airports for "Arkansas", then another 3 airports for "Arizona" below "Arkansas", it will return inaccurate results when searching for "Arizona"... MATCH will return the first row where "Arizona" was found and COUNTIF will return 10 for the [height], which will include the first 7 "Arizona" airports plus the first 3 "Arkansas" airports.

Also, the cell reference used as the first argument of the OFFSET function should be one row above the start of the lookup_array. In this case, the lookup_array was $B$3:$B$385 (starting on row 3), so the cell reference was $H$2 (row 2). If you're referencing a cell that's NOT one row above the lookup_array, you will need to adjust the result of the MATCH function accordingly (add or subtract the appropriate number of rows).

Lastly, if any leading or trailing spaces exist in some of the records in the lookup table, it will return inaccurate results. If you think this may be a contributing factor, you can clean the data in the "State" column using the TRIM function (then use Copy > Paste Special > Values to overwrite the original values).

Double-check your formula syntax... if everything looks correct and you're still getting some instances where the results are off by multiple rows, please share a sample copy of the workbook and indicate which "instances" are off, and I'll see if I can determine the source of the problem.

Kind regards.