MATCH and data validation

[JFerrer23]

Hey yall!

I'm trying to summarize employee hours per week in a chart which you can change employee and week on data validated lists.

I'm testing out MATCH functions for an INDEX, but I'm getting #N/A errors.

1. The "Supervisor" list seems to work, except for a single name "Alix."

2. The "Date" list does not work referencing the data validated cell. MATCH does seem to work when directly referencing the table (orange) "Date" is drawing from.

The arrays referenced in the MATCH functions are on the second sheet.

Any assistance would be much appreciated.

Thank you!

[#2 · Accepted solution]

@JFerrer23 

1) Default match type with missed third parameter

is 1, which requires ascending order of the range. That's not your case,

Alix shall be before Ana for such mode. Use exact search instead

2) Names of table columns are not actually the dates (which internally are numbers) but texts which represent dates. Convert lookup value to text as well

[#3]

@Sergei Baklan 

Brilliant, works perfectly! Thank you so much! :D

[#4]

@JFerrer23 , you are welcome

[#5]

Hey @Sergei Baklan,

I've set up the dates as text which allows most of the dates to work, but selecting a date in 2020 brings up an error. I've tried put the dates in "ascending order" like how the names should be, but that didn't work either.

[#6]

@JFerrer23 

You have text representation of dates, not actually dates (which are numbers), thus they are sorted as texts.

You shall use third parameter (zero) in match for exact match (see attached), or use dates, not texts, both in titles and in drop-down list.

[#7]

@Sergei Baklan

Got it! I didn't realize how important that parameter is.

Thank you so much!

[#8]

@JFerrer23 , glad to help