Dropdown box Data in formula

DBANE_ALAMO

Hello all, and thank you in advance for any help that you can provide. I am using a dropdown box, single selection, to select a date that determines data pulled from a table. I have 10 data values for each date in the table. I am attempting to use VLOOKUP to collect the data with the date from the dropdown box as the search value. My formula is:

=VLOOKUP([Current Week]4, {Mantis 2 Project Summary Data Range 1}, {Mantis 2 Project Summary Data Range 2}, false)

i have tried formatting the first column in the table, to match the date from the dropdown box to, as both date and text/number and neither is working. the error message i am receiving is '#INVALID DATA TYPE'.

Dennis

Paul Newcome

Are you able to provide some screenshots that show exactly what you are working with?

Initially I would say that there is an issue with the VLOOKUP. The third portion should be a number that represents which column number the VLOOKUP is pulling from with the far left column being 1.

=VLOOKUP([Current Week]4, {Mantis 2 Project Summary Data Range 1}, #, false)

Tamara Gordon Berlik

First let me recommend using a INDEX(MATCH) formula over the VLOOKUP. While the VLOOKUP works it can cause the system to lag slightly when the system is looking at a lot of data. Here is a step by step explaining the INDEX(MATCH) formula ==INDEX({Sales rep ref Range 3}, MATCH($[Customer Name]@row, {Sales rep ref Range 4}, 0)) -explanation of how to look at the formula. INDEX({column in ref sheet that has the value you are looking for and want returned, in this formula it is the Sales Manager or CSS}, MATCH($[Current sheet customer name]@row, {column in ref sheet that has the Customer name in}, 0)) The $ freezes the cell you are looking at so no matter where you drag the formula it will always be the customer name cell you look at. The @row states that you are only looking at info in the current row of the current sheet. The 0 means you want an exact match return. I also like to use the IFERROR with this type of formula so that if there is nothing to return or no match I can get "no match" to show.

In your VLOOKUP formula you do not have a Column # telling the formula what value you want returned. Here is an example of what I am stating. ==VLOOKUP($[Customer Name]@row, {Sales rep ref Range 1}, 2, false) - The first thing to know your lookup sheet has to have the column that you are looking up in the farthest left position and then the columns to the right you can return the values. For this the customer name in the reference sheet is column 1, the sales manager is column 2 and the css is column 3. =VLOOKUP($[current sheet column looking to match]@row, {reference sheet with all columns highlighted that will have the look up value and any row that may have the return value, in Sales rep ref I highlighted the Customer name, Sales manager and CSS columns}, 2, false) with this formula you would use the same formula and change the column number to match what you want returned. The "false" means return an exact match. You can also apply the IFERROR to this formula as well.

Your formula also seems to have to many lookup columns. I am assuming that the Date Range 1 is the one you are looking at and the 2 is the one you want returned, with this assumption when creating the Date Range 1 you would highlight the entire table with the Column your are looking up in the farthest left position of the table you highlight this is position 1, and lets just say the cell you want to return is in position 3 or the 3rd column from the left. You can return anything for the error you like, I use No Match because my eyes are trained to watch for that as long as you have them in open and close quotations you can add anything. You would right it like this- =IFERROR(VLOOKUP([Current Week]@row, {Mantis 2 Project Summary Data Range 1}, 3, false), "No Match")

 

DBANE_ALAMO

Good afternoon Paul,

i tried using the number for the column to pull from, that didn't work so i used the column name, and that didn't work.

thank you for your help.

Here is my data set:

Here is my table i am pulling the data into:

DBANE_ALAMO

Good afternoon Tamara,

I will see if i can get the Index/match formula to work with your explanation. I tried it earlier with the guide of the smartsheet help, and did not understand how it was going to pull the one cell of data, it looked like it would only pull the column data.

I had previously tried using the column number in the formula, and that did not work so i changed it to the column name, that did not work either.

thank you for your help

Paul Newcome

You are going to want to make sure you select the appropriate columns when setting up your range in your cross sheet reference. The far left column should be the Week Column, and the far right column should be whichever column you are pulling from. If you are planning on using the same formula in other columns to pull from other columns, you can include these as well when setting up the range, but you will need to make sure that at the very least the column to pull from is included. You would then count starting at 1 in the far left column, and whatever number you end up on for the column to pull from will be the number that you put in the VLOOKUP.

DBANE_ALAMO

Hey Paul,

i re-verified that my table has the 'week' column on the far left and all of my other columns are included. i have adjusted and looked at this thing so much my eyes are starting to cross. I updated VLOOKUP formula with the number for the column, now the error message receive is '#NO MATCH'. Yet, i can clearly see that the date 01/08/19 is in the Week column second row down of the data table.

Paul Newcome

Are the columns in both sheets formatted as Date type columns?

DBANE_ALAMO

the column that i am pulling the date from for the search value is a dropdown box. i have tried formatting the column in the data set as date and as text/number and both provide the same results. '#NO MATCH'

DBANE_ALAMO

Update:

i did an experiment and pulled two dates from the data table, the largest and smallest of the Week column. when using these dates (not the dates from the dropdown box) the VLOOKUP formula works.

So, it looks like the issue has something to do with the format of the data in the dropdown box.

Paul Newcome

If you are populating dates via a dropdown then they are being stored as text values and not dates.

Joseph Gentile

@Paul Newcome - Any ideas on how to get an INDEX/MATCH formula to find an exact match if the matching dates come from a Dropdown Column & a Date Column?

We're attempting to create a concept and are not been successful yet. I think it's because the dropdown is formatted at "Text" and the Index columns are formatted as dates.

The reason we have to use the dropdown of dates is that it's tied to a Form and we only want specific dates listed as options. We have a "read ahead" process for agenda items that go to our Board of County Commissioners. This is why there's a specific list of available dates.

We have the 2nd sheet with multiple date columns for different phases. As for project management, we have specific dates required based on the original dropdown date.

Joseph Gentile

We found a working solution! All you need is one helper column. And by “we” I mean One of my awesome colleagues and Smartsheet support! 🤣

below is the info from Smartsheet support

Hello,

Thanks for contacting Smartsheet Support. We spoke the other day about getting a Drop down value column to pull in a Date column and use that in an INDEX/ MATCH, I'd be happy to further assist you with this.

Through extensive testing and consulting on my end unfortunately we have not found that there is a way to pull a date out of a drop down value column and have it work with INDEX/ MATCH. 

I have only been able to get the helper column to pull in a date using the following formula: 

 

• =DATE(VALUE(RIGHT([Meeting 1 date]@row, 2)), VALUE(LEFT([Meeting 1 date]@row, 2)), VALUE(MID([Meeting 1 date]@row, 4, 2)))

However if you continue to use this formula for example to pull in from the INDEX/ MATCH it will not work. You will continue to get a NOMATCH. Even when using the helper column since the source is the drop down value it will not read properly.

I will let our Product team know that you would like to see this Enhancement Request. 

Paul Newcome

@Joseph Gentile Sorry for missing your last post. My notifications can get a little crazy sometimes and every now and then one will fall through the cracks.

The reason the DATE formula provided by support wasn't working to match on is because they are not outputting 4 digits for the YEAR portion. Lets just say that [Meeting 1 Date]@row is 12 Dec 2022. Their formula is populating the DATE function like so:

=DATE(22, 12, 12)

This tweak should help with that:

=DATE(VALUE("20" + RIGHT([Meeting 1 date]@row, 2)), VALUE(LEFT([Meeting 1 date]@row, 2)), VALUE(MID([Meeting 1 date]@row, 4, 2)))

Joseph Gentile

Thanks, @Paul Newcome! No worries. I had cast a lot of lines that day, hoping to get at least one bit! I appreciate the extra tip on the DATE function. That did help and we have a working solution! 👏

Paul Newcome

Happy to help. 👍️