Stumped on a VLOOKUP formula

Carolyn H

I'm very new to Smartsheet and feeling in over my head.

I have a sheet called "FH Course Database." It lists all the courses at my school in a column named "Code Code", e.g. in the screenshot below it's "ACTG F001A"

In that same sheet have a column named, "Formula Test" formatted as a checkbox. (It's the fifth column in the screenshot. The one that says #UNPARSEABLE.)

I want to have Smartsheet search column 3 (named "ZTC") of another sheet called, "ZTC Mapping Survey" and check the box in "Formula Test" if it's there.

No matter how I set it up I either get an UNPARSEABLE error or one of several other errors. At the moment I'm trying "=VLOOKUP({ZTC Mapping Survey}[Code Code]@row, 3, true)" but another I've tried is =VLOOKUP({ZTC Mapping Survey}[Smartsheet Code]@row, 3, true).

I'm stumped. Any help is appreciated!

Carolyn

J Tech

Hi @Carolyn H

Based on your description, it sounds like you want to check the checkbox in the "Formula Test" column in the "FH Course Database" sheet if the value in the "Code Code" column exists in the "ZTC" column of the "ZTC Mapping Survey" sheet.

Here's a formula you can try in the "Formula Test" column:

=IFERROR(VLOOKUP([Code Code]@row, {ZTC Mapping Survey}, 2, FALSE), FALSE)

This formula uses the VLOOKUP function to look up the value in the "Code Code" column of the current row in the "ZTC Mapping Survey" sheet. If a match is found in the "ZTC" column, the formula returns TRUE, which will check the checkbox in the "Formula Test" column. If no match is found, the formula returns FALSE, which will leave the checkbox unchecked.

Note that in the VLOOKUP function, we are referencing the entire "ZTC Mapping Survey" sheet as the lookup table by using curly braces {}. The 2 in the formula refers to the column number of the "ZTC" column in the lookup table, and FALSE indicates an exact match should be returned. The IFERROR function is used to handle cases where a match is not found, which prevents the UNPARSEABLE error you were seeing before.

I hope this helps!

Carolyn H

Thank you for your answer! I really appreciate it.

I understand why the solution you provided should work. The good news: it doesn't say UNPARSEABLE anymore, yay! The bad news - it doesn't give me a check in the boxes of cells that should be checked. E.g. financial accounting is indeed in the ZTC column of the mapping survey sheet, but on the database (with the new formula) the checkbox remains unchecked.

I'm the owner on both sheets so it's not a permissions thing. I don't have any columns hidden on either sheet so I'm not accidently referencing the wrong column.

Could it be because column "ZTC" in the ZTC Mapping Survey sheet uses a formula? It's

=[Accounting ZTC]@row + [Allied Health Sciences ZTC]@row + [Adaptive Learning ZTC]@row + [Anthropology ZTC Courses]@row + [Art Courses]@row + [Astronomy ZTC Courses]@row + [Athletics ZTC courses]@row + [Biology ZTC]@row + [Business ZTC]@row + [Computer Science ZTC]@row + [Chemistry ZTC]@row + [Child Development ZTC]@row + [Counseling ZTC]@row + [Comm studies ZTC]@row + [Creative Writing ZTC]@row + [Dental Assisting ZTC]@row + [Dental Hygiene ZTC]@row + [Dance ZTC]@row + [Diagnostic Medical Sonography ZTC]@row + [Economics ZTC]@row + [Education ZTC]@row + [EMS ZTC]@row + [English ZTC]@row + [Engineering ZTC]@row + [English for Second Language Learners ZTC]@row + [Ethnic Studies ZTC]@row + [Geography ZTC]@row + [GID ZTC]@row + [GIST ZTC]@row + [Global Studies ZTC]@row + [History ZTC]@row + [Health ZTC]@row + [Horticulture ZTC]@row + [Humanities ZTC]@row + [Internship ZTC]@row + [Japanese ZTC]@row + [Journalism ZTC]@row + [Kinesiology ZTC]@row + [Language Arts ZTC]@row + [Library ZTC]@row + [LINC ZTC]@row + [Mathematics ZTC]@row + [Media Studies ZTC]@row + [Music Tech ZTC]@row + [Music ZTC]@row + [Noncredit ZTC]@row + [Pharm Tech ZTC]@row + [Photography ZTC]@row + [Phys Ed Adaptive PE ZTC]@row + [Physical Ed ZTC]@row + [Philosophy ZTC]@row + [Physics ZTC]@row + [Political Sciences ZTC]@row + [Psych ZTC]@row + [Physical Science ZTC]@row + [Radiologic Tech ZTC]@row + [Resp Ther ZTC]@row + [Sociology ZTC]@row + [Soc Sci ZTC]@row + [Spanish ZTC]@row + [Special Ed ZTC]@row + [Theatre ZTC]@row + [Vet Tech ZTC]@row + [Women's Studies ZTC]@row + [IDS ZTC]@row

This results in some of the cells in the ZTC column containing multiple values, while others contain only one. When tested against a cell that has only one, though (e.g. ACTG F001A Financial Accounting I) it still doesn't work.

Any ideas?

treyrosejohnson

Hi Carolyn,

Can you explain a little more? What is the checkbox supposed to note? Do you want it to automatically check the box if the ZTC column is blank? Or if it isn't blank?

I can also see a potential problem in that you are using the Code Code as the Search Value, but that isn't going to match up with the information in the ZTC column because that is the Code Code PLUS other information. You would need to use an approximate match, which can be tricky. Are all the Code Codes the same number of character? Are they all 'XXXX XXXXX'?

treyrosejohnson

Hello again Carolyn,

Try this formula:

=IF(COUNTIF({ZTC Mapping Survey}, [Smartsheet Code]@row) > 0, 1, 0)

I believe this will work for any row with only 1 value, but I don't think it will work for rows with multiple values. I don't think using an approximate match will work either because the Course names are so similar. If you have approximate match on for a VLOOKUP formula, it may look for FINANCIAL ACCOUNTING II and get to FINANCIAL ACCOUNTING I and stop looking because that is close enough for it to consider it a match. I'm no expert, but I am struggling to find a way for it to give you the results that you want if there are multiple values in one cell.

Carolyn H

Thank you so much for the quick response!

I just played around with it and it looks like you're exactly right. When I apply your new formula in the Course Database "Formula Test" column, I get a checkbox for Financial Accounting I. YAY! But if I go over to the mapping survey and add an additional course (e.g. Art I) to the "ZTC" column the checkbox for Financial Accounting I disappears. Having more than one value broke it. Interestingly, applying it as a column formula also didn't result in any other boxes checked even for courses coming from a cell with only one entry. The only one I can get to return a positive result is Financial Accounting I.

The Code Codes have varying numbers of characters, unfortunately.

Using [Smartsheet Code] with an exact match seems like the way to go but doesn't solve the multiple values in one cell problem. I'm stumped again.

Is there any possiblity a different function might be compatible with looking for one data point in a cell with multiple data points?

treyrosejohnson

Carolyn,

The formula should work for any item with only one value when you apply it as a column formula. I made myself a small scale mockup of your problem to try and solve it and was having the same issue until I reloaded the Smartsheet. Not sure why, but it needed to be reloaded.

I don't know of any formula or function that will look through multiple values in a cell and return only the one you are looking for. It may be possible, but that would outpace my skillset.

Good luck and I hope you are able to find a solution that totally solves your problem!

treyrosejohnson

Carolyn

I just saw another thread on here that may have solved your problem.

Try this:

=IF(CONTAINS([Smartsheet Code]@row, {ZTC Mapping Survey}), 1, 0)

I'll keep my fingers crossed!