Advanced Formula #No Match Error

Cristina Ayala

Hello,

I have a master sheet that I am referencing which contains multiples rows that I need reflected on my teams sheet. There is a column named "Vuln Mgmt" (set as a dropdown/multi-select) that has options Yes: 3rd party, Yes: Security, or No. I want to create a formula that will reference the entire master sheet and look for the phrase "Yes: Security" then feed out the data in the first column of that row.

I recycled another VLOOKUP formula I used to pull data in from the master sheet, the only difference being that instead of putting a specific keyword for the search value, I referenced a cell on my teams sheet.

Formula being used in my team sheet that works: =VLOOKUP([Customer Name]@row, {VulnMgmt Data Set}, 3, false)

New formula that throws 'No Match': =VLOOKUP("Yes: Security", {VulnMgmt Data Set}, 1, false)

I am not sure if VLOOKUP is the best formula for this or how to move forward without manually updating this. Let me know if additional details are needed.

Victoria_Indimar

Hello!

Using the Vlookup means it'll check the first column of the dataset table to see if it matches the "Yes: Security". If your dataset table does not have those values in the first column, then it won't find a match (No Match). If it is in the first column, then it will output whatever you have in the third column (since you have the "3" and the col_num) where it finds the first matching value. So based on what you explained above, I don't think your dataset table is setup for the Vlookup results you're looking for.

Instead of Vlookup, I would recommend using Index/Match. This formula gives you more flexibility on the order your columns because you're just selecting the columns specific to the data you want to output and data you want to compare against (and results in processing less data). This also means if you change column orders it will not affect your formula.

Lastly, in cases where there really is not a match and you don't want it to show "No Match" as the results, you can add IFERROR to the front of your formula so it'll output something other than "No Match" like leaving the cell blank.

Hope this makes sense. Please let us know if this helps!

Cristina Ayala

Thanks @Victoria_Indimar, this helps some but now I am running into a new issue. I get the error #Invalid Ref. Here is some more context around my ask. I want to pull in only the Customers that have "Yes: Security" in VulnMgmt column. Then I would ideally only have Customer Three and Four in my teams sheet, and can write formulas to pull in additional details from the master sheet, such as GM and Site Manager.

New Formula Attempts

=INDEX({VulnMgmt Customers}, MATCH("Yes: Ellucian", {VulnMgmt Coverage},0))

=INDEX({VulnMgmt Customers}, MATCH([Coverage Reference]8, {VulnMgmt Coverage}, 0))

(In the last formula, I just selected a random cell on a new master sheet that only contains "Yes: Security")

Let me know where there is a flaw in this logic or best solution. Thank you!

Victoria_Indimar

Ah, okay, I think I misunderstood. Index/Match and Vlookup will output the first value it finds that matches that criteria, it will not output the multiple rows that matches. So in your example above, it will output Customer Three because it's the first one it found, but it won't list Customer 4.

If you want all the customers listed in one cell with VulnMgmt Coverage = Yes: Security, you could do a Join/Collect. But if you want a listing of all customers in rows and other fields that have the VulnMgmt Coverage of a certain value, would a report work for that view instead?

Maybe I'm missing something else in your scenario? Can you show what you would want the results to look like in your second sheet?

Here's an example of a report view that groups by VulnMgnt Coverage:

Cristina Ayala

Sure, here is a mock image of the master sheet followed by a mock image of what I would like my team sheet to look like. After relevant data from the master sheet is pulled in, an automation will use the Site Manager contact to email them an update request which will update the row with more details specific to my team.

Cristina Ayala

@Victoria_Indimar , Do you know of any formula that has the ability to output more than just the first result of a match?

Victoria_Indimar

Hi Christina,

From what I understand, a second sheet should not be needed for what you're trying to do. An automation would identify all the applicable rows, notify the site manager email, and request the update. This would not require them to go into the sheet to view either, if you're wanting to limit. And if multiple rows apply to the same manager, it'll be sent in one notification and allow them to update each within that one Update Request.

Trigger: When "Relevant Data" is populated and matches X

Condition: VulnMgmt Coverage = Yes: Security

Action: Update Request to specific email in field (site manager email), includes columns of info you want them to update.

Does that help?

If you still want a specific view, a Report would get you that limited view (filtered down) and you can still send that Report to the Site Manager on a regular cadence (ex. daily, weekly, etc. The report notification would not trigger by certain data - that's what the sheet automation would do). And then from the Report, the Site Manager could still update the fields you need them to fill out which in turns updates the sheet.

Cristina Ayala

The only reason we have to have a separate sheet is because our department requires additional information (4 columns worth and attachment) that is not housed on the master sheet. The master sheet team also does not want to own or maintain the process for obtaining the additional data so we were advised to create a new sheet referencing theirs. Out of their sheet, which has approximately 10 columns, I only care about 4 of them (Customer, GM, SM, and Tech Lead).

I attempted to pull in the VulMgmt field as a way to pull in the customer name but all efforts have failed. The only solution I was able to come up with, which is not ideal, is to have an automation notify me when a new customer is added to the master sheet and manually add them to mine. I am hoping there is another formula that I have no discovered that is able to use the following logic:

If VulnMgmt (column 8) equals "Yes: Security", then output that data from Customer (column 1), if false then skip or leave blank.

Victoria_Indimar

@Cristina Ayala Ah, I see now. Yeah, I don't think a formula would do that for you since it'll only populate the field that the formula is in, not output a range into multiple row.

Could you do an automation from the master sheet to copy that row to your sheet (instead of just notifying you)? It would mean all the columns are added to your sheet but the ones you don't need you could hide/lock.