Partial Match for a Vlookup

Hi All,

I am looking for some help with a formula. I want to search for a particular value in a cell that may include multiple values i.e I want to locate A123 but the cell from the reference sheet contains A123; B456.

My thought was to use a partial vlookup but from what I have read it does not look like wildcards can be used and I haven't been able to leverage the contains function either.

Would anyone have any suggestions?

Find more posts tagged with

Formulas

Accepted answers

All comments

Tim Shaded

This sounds like it should be simple enough to accomplish with something like this:

=COUNTIF({Reference Sheet Column}, CONTAINS([Value Searched For]@row, @cell))

Reference Sheet Column - represents a reference from another sheet which is being audited for entries.

Value Searched For - represents a column on the same sheet where the formula is entered for tabulation. In that column each row would allow for entry of the appropriate value being searched for (ie. A123).

COUNTIFS can be used if multiple criteria need to be met.

Jonathon Kavanagh

Thanks Tim, is there a way to go one step further and pull back a different data point from the reference sheet i.e. I want to look for A123 in column A which contains A123; B456 and then return the value in column B?

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.