Inquiry How to vlookup

Hi Community,

Hope all are doing well,

I need to make Sheet 3 dynamic, i.e., if I add any data to Sheet 1 and Sheet 2, it should be merged using the same ticket number in One Raw.”

Desire Result:

I m attaching 2 screenshots please suggest how to achieve this.🤗

Find more posts tagged with

Best Practice

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Lil Na$is

You must use two different ranges to VLOOKUP from two sheets.

Ticket Number =IFERROR(INDEX({assigned_ticket Range Ticket Number}, No@row), "")
Created Date =IFERROR(VLOOKUP([Ticket Number]@row, {ticket_system_form Range Ticket Number: Issue}, 2), "")
Re Assign By =IFERROR(VLOOKUP([Ticket Number]@row, {assigned_ticket Range Ticket Number: Approver}, 2), "")
Assign To =IFERROR(VLOOKUP([Ticket Number]@row, {assigned_ticket Range Ticket Number: Approver}, 3), "")
Assign Date =IFERROR(VLOOKUP([Ticket Number]@row, {assigned_ticket Range Ticket Number: Approver}, 4), "")
Approver =IFERROR(VLOOKUP([Ticket Number]@row, {assigned_ticket Range Ticket Number: Approver}, 5), "")

The [Ticket Number] column automatically gets the number as the values get added in the Assigned Ticket sheet using the [No] column's value as the row index of the INDEX function.

(I usually use the INDEX & MATCH functions as they cope with column position change. However, I found using the VLOOKUP function saves time as I do not have to create multiple cross-sheet reference ranges. Thanks for bringing this to my attention. Below is an example when I use INDEX & MATCH )

=IFERROR(INDEX({assigned_ticket Range Re Assign By}, MATCH([Ticket Number]@row, {assigned_ticket Range Ticket Number})), "")

https://app.smartsheet.com/b/publish?EQBCT=b2944535100a46e384de83531475f8c0

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.