Need Help With Type of Formula to Use (VLookup with Index/Match), Index/Match, VLookup/IF/AND??????

Syoung113

Hi, I have two sheets that I am trying to pull from. I want to pull the "Service Date" from the "Service Integration Sheet" and put it onto the "Account Management Sheet".

I am trying to match accounts that have completed their "Onboarding Call". The common variable on both sheets is the Sales Order Number, but I think the issue might be that there are several rows of the SO on the Service Integration Sheet. I have tried so many different formulas and they never work. I uploaded an Excel File with both sheets on separate tabs.

SmartSheet Formula.xlsx

Uploaded Feb 20, 202517.58 KB

Can someone help?

Amit Wadhwani

Hi @Syoung113

You need to create cross-sheet references

1. In the Account Management sheet, right click on any cell and and then click "Manage References"
2. In the window that opens, click on Create button.
3. In this window, search for Service Integration Sheet and select the "Sales Order Number" column by selecting the header. At the top, type the Sheet reference name as "SalesOrders"
4. Click on Insert Reference.
5. Repeat the process and select "Account Name" column. Name this reference as "AccountNames"
6. Use this formula in the "Account Name" column in the "Account Management" sheet
   =INDEX({AccountNames}, MATCH([Opportunity_r: Sales Order Number]@row, {SalesOrders}, 0))

You might have to update the formula to refer to the actual column name which is not fully visible in the screenshot. I assumed it is "Opportunity_r: Sales Order Number" and the formula is created accordingly.