Formula VLOOKUP or maybe an alternative solution

Hello,

I am trying to develop a formula that will reference another sheet and bring back the content from a specific cell. Need to reference two columns for the match in the reference sheet. The match component will come from a column [PI or Account #] on the main sheet to be searched in column 1 on reference sheet and then the next match needs to be for Column 2 for "travel" (there are three categories we will eventually search/pull from travel, supply, service).

What I am trying in various forms:

=VLOOKUP(IF([PI or Account#]@row , {AJ_Number}, 1, false), AND({AJ_Name}, 2, @row = "Travel", {AJ_desc})

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Happy to help. 👍️

You can use an IFERROR to output a blank or any other text in the even that there is no match in the source sheet. I just always wait to apply that until I have ensured my formula is in fact working as intended because the IFERROR can be a pain when trying to troubleshoot.

=IFERROR(INDEX(COLLECT({Column To Pull Over}, {AJ_Number}, @cell = [PI or Account #]@row , {AJ_Name}, @cell = "Travel"), 1), "")

All comments

Paul Newcome

You would need an INDEX/COLLECT combo for this:

=INDEX(COLLECT({Column To Pull Over}, {AJ_Number}, @cell = [PI or Account #]@row, {AJ_Name}, @cell = "Travel"), 1)

Angela Hollingsworth

Okay tried this and got an #invalid value -

Angela Hollingsworth

I see why - It is because its blank in my sheet!! Thanks got it now

Paul Newcome

Happy to help. 👍️

=IFERROR(INDEX(COLLECT({Column To Pull Over}, {AJ_Number}, @cell = [PI or Account #]@row , {AJ_Name}, @cell = "Travel"), 1), "")

Angela Hollingsworth

Thanks this worked out perfectly!

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.