Forum Discussion
Data Validation List with multiple choices based on one cell (w/o VBA)
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.
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!
I am getting instances where my reference is off by multiple rows. Do you know how to fix this in the reference column?
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.