Inquiry How to vlookup

Lil Na$is

Hi Community,

Hope all are doing well,

Sheet 1png.png

Sheet 2png.png

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:

Column: Ticket Number | Created Date | Assing By | Assing To | Assigned Date | Approver

DATA : CH000000001 | MM-DD-YYYY | JUAN CARL | JEAN CARL | MM-DD-YYYY | JUAN FLOW

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

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