Cross-sheet references with Index/Match and reference sheet has multiple rows that match

JSpears

Sheet A:

Formula in Data: Ent. Install Time: =IF(CONTAINS("Enterprise 2022 R1", {AtRiskAnalysis-Version_Version}), INDEX({AtRiskAnalysis-Version_EntInstallTime}, MATCH(CustomerId@row, {AtRiskAnalysis-Version_CustomerID}, 0)))

Formula in Data: EDIS Install Time: =IF(CONTAINS("EDIS 2022 R1", {AtRiskAnalysis-Version_Version}), INDEX({AtRiskAnalysis-Version_EDISInstallTime}, MATCH(CustomerId@row, {AtRiskAnalysis-Version_CustomerID}, 0)))

Sheet B: AtRiskAnalysis-Version

Formula in Enterprise Install Time: =IF(CONTAINS("Enterprise 2022 R1", Version@row), (ROUND(NETDAYS([GA Ent 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0)), "")

Formula in EDIS Install Time: =IF(CONTAINS("EDIS 2022 R1", Version@row), (ROUND(NETDAYS([GA EDIS 2022 R1]#, [Go-Live Date]@row) / (365.25 / 12), 0)), "")

PROBLEM: Sheet A is not showing "4" in the C-10301 Data: Ent. Install Time

I assume this is because there are multiple rows in the referenced sheet for the C-10301 ID. Is it possible to have the formula look at all instances of C-10301 to find the Enterprise Install Time or the EDIS Install Time?

Thank you in advance!

AravindGP

Hi @JSpears

The issue with the current formula is that you're looking for a value in a column and trying to fetch the first response for the match, which is your customer ID. Since the first response is blank, the value shown is blank. You will need a response which has both the customer ID and the version you're looking for. Try this formula instead.

 =IF(CONTAINS("Enterprise 2022 R1", {AtRiskAnalysis-Version_Version}), INDEX(COLLECT({AtRiskAnalysis-Version_EntInstallTime}, {AtRiskAnalysis-Version_Version}, "Enterprise 2022 R1", {AtRiskAnalysis-Version_CustomerID}, CustomerId@row), 1))

JSpears

@AravindGP - Thank you! That worked perfectly for the Enterprise Install Time. But I can't get it to work for the EDIS Install Time. I'm thinking it's because the Version column that I'm looking at needs a "contains" statement because that column will begin with "EDIS 2022" but it won't always be an exact match.

So...I tried this:

=IF(CONTAINS("EDIS 2022", {AtRiskAnalysis-Version_Version}), INDEX(COLLECT({AtRiskAnalysis-Version_EDISInstallTime}, CONTAINS("EDIS 2022", {AtRiskAnalysis-Version_Version}), {AtRiskAnalysis-Version_CustomerID}, CustomerId@row), 1))

But it's returning "#INCORRECT ARGUMENT"

Thank you!

Jennifer

AravindGP

Hi Jennifer,

Modify the formula as below to get the value. You can replace EDIS 2022 with any other value when it is not an exact match, like EDIS 2021 or EDIS 2019

 =IF(CONTAINS("EDIS 2022", {AtRiskAnalysis-Version_Version}), INDEX(COLLECT({AtRiskAnalysis-Version_EntInstallTime}, {AtRiskAnalysis-Version_Version}, CONTANS("EDIS 2022", @cell), {AtRiskAnalysis-Version_CustomerID}, CustomerId@row), 1))

JSpears

@AravindGP - I think we are closer, but Smartsheet still doesn't like it:

=IF(CONTAINS("EDIS 2022", {AtRiskAnalysis-Version_Version}), INDEX(COLLECT({AtRiskAnalysis-Version_EDISInstallTime}, {AtRiskAnalysis-Version_Version}, CONTANS("EDIS 2022", @cell), {AtRiskAnalysis-Version_CustomerID}, CustomerId@row), 1))

I think it's a ( or ) issue.....but I'm just not sure where.....

AravindGP

Hi Jennifer,

I tried the same formula in my instance and it works fine. What is the error you get? If you prefer to do a screenshare, you can send me an invite at aravind.gp@atturra.com

JSpears

I figured out the issue - "Contans" vs. "Contains"

Thank you so much for all of your help!