Using the v-look up function

Jessica Howell

Hello,

I am trying to use this formula, =VLOOKUP([Facility ID]1, {Data Control Range 1}, 3, false) and it continues to come up no match or incorrect argument set, is there anyone able to help figure out whats going on here?

Paul Newcome

There it is...

You are only selecting 2 columns, but you are telling the formula to pull from the 3rd column. Change the 3 to a 2 and see what that does for you.

Jessica Howell

It worked! Thanks so much... i have tried changing between a 2 and a 3 previously but never thought multi-select would be an issue! Really appreciate it

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.

Andrée Starå

Hi Jessica,

What are in the ranges?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jessica Howell

andree@getdone.se

I work in supply chain, I am trying to create user-friendly process for reporting spills across the business. There will be a universal form for the incident reporter to fill out, and i would like the facility name and address to auto fill once their facility id has been entered. This particular formula is in regards to the facility name, i assume once i can get this one the others will be fairly simple... hopefully.

1st attachment, main sheet, where we want the information to appear. 2nd attachment, where the data is being pulled from.

Paul Newcome

How is the data in the [Facility ID] column on the Data Control sheet populated? Is it possible that one is an actual numerical value (Data Control sheet) and the other is being stored as a text string (Environmental Incident Tracking sheet)?

Jessica Howell

I entered all of the data onto the data control sheet. On the environmental incident tracking, the facility id comes from someone entering it on the form

Paul Newcome

Yeah. It looks like the problem is the number vs text string issue.

In the form... Is it a single or multi select dropdown, or is it free text?

Jessica Howell

For the facility id, it is a dropdown, the others are free text

Paul Newcome

Single select or multi select dropdown?

Jessica Howell

Sorry, multi-select

Jessica Howell

Any ideas?

Paul Newcome

Sorry about that. I didn't get any notification you had replied that last time.

Will people be selecting multiple options in the multi-select column on the form? If you switch the column type over to single select, the VLOOKUP will work as is. If you have to leave it as a multi-select, it really complicates things. Especially if someone makes multiple selections within the same cell.

Jessica Howell

I have taken the multi- select off the first column (facility id) and changed it to text/ number. Only 1 entry, and it doesn't matter how it's entered

Paul Newcome

Do you mean it is still not working? I did some testing yesterday. That's how I know that the multi-select can get messy, but a single-select would work fine. Text/number should be working fine as well.

What columns are covered by your range?

Jessica Howell

Still not working. I type in the formula, it calculates and then gives me incorrect argument set. (Data Tab) I am shift clicking columns Facility ID and Facility name. The pictures i have attached i have removed the values, but everything else is filled, when i try a value with the corresponding fields filled in, it still doesn't work.

Paul Newcome

There it is...

You are only selecting 2 columns, but you are telling the formula to pull from the 3rd column. Change the 3 to a 2 and see what that does for you.

Jessica Howell

It worked! Thanks so much... i have tried changing between a 2 and a 3 previously but never thought multi-select would be an issue! Really appreciate it