Partial Vlookup

Hi,

I need to lookup partial value and map the tagging.

There are 2 sheets, main data sheet which is connected with lookup sheet to fetch the Tag.

Wherever complete match is found, tag is fetched but i also want to fetch tag in case of partial match i.e. Row3="Yellow" (as DE19-1121 in lookup sheet says Yellow which is also mention in Batch)

Similarly Row6 should say "Green", Row7 should say "Blue" and Row8 should say "Yellow"

Looking forward for the solution, thanks

Find more posts tagged with

Formulas

Accepted answers

ker9

@Gagan Kalra

In the lookup sheet, it appears from your snips, that you are only looking for items that begin with"DE", is that correct?

All comments

Gagan Kalra

@Paul Newcome - Can you please help, saw your answers in other post but can't solve this

Hudson_Miller

@Gagan Kalra I got the below to work. I'm not sure how many "colors" you are working with as Tag's but if it's just a handful you can use nested IF's as captured below to inspect the Batch Number column in your main sheet for instances of the batch number from your lookup sheet and if found return the corresponding Tag color. This is done by setting the range for the lookup BN(colorX) to a single cell in the lookup and setting the "yes" value of the IF statement to a single cell range of corresponding color. See screen shots below.

Hudson

Gagan Kalra

Thanks @Hudson_Miller, but tag color will keep on increasing, this solution will be good until finite tags but i'll will have many tags

Hudson_Miller

@Gagan Kalra

The issue here is that index match and other formulas expect the 'search' criteria to be specific - in your data it appears that the value you want to search for is co-mingled. I'm going to tap out and hope that others can help you.

SoS | Dan Palenchar

COLLECT() and CONTAINS() could help here

ker9

@Gagan Kalra

In the lookup sheet, it appears from your snips, that you are only looking for items that begin with"DE", is that correct?

Gagan Kalra

https://community.smartsheet.com/discussion/comment/483761#Comment_483761

Yes, that's right

If in Batch Column main sheet, there is text which is matching with Lookup sheet Batch, i need to pick up Tag

ker9

@Gagan Kalra

To confirm, you are not looking at the batches that begin with "RE" or "NE", which appear in your screenshot, just "DE".

Gagan Kalra

https://community.smartsheet.com/discussion/comment/484243#Comment_484243

Yes, that's right

for example, Row6 should say "Green", Row7 should say "Blue" and Row8 should say "Yellow"

As it is partially matching with Lookup batch

Hudson_Miller

@Gagan Kalra

Try this:

Set up a helper column, I called mine "Parse." The formula for "Parse" is:

=MID(Batch@row, FIND("DE", Batch@row), 9)

This will pull out the "DE" entry.

Then construct a lookup sheet "Tag Lookup" where all the various DE Entries exists in one column "DE Number" and the corresponding colors in a second column "Tag".

Then write your Tag formula to be something like:

=INDEX({Tag Lookup | Tag}, MATCH(Parse@row, {Tag Lookup | DE Number}, 0))

Good Luck - Hudson

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.