Nested

Hi All

I am trying a nested index match - the index match and + "" text part work - but i wanted to add a twist - so when the information requested returned - only when 09-Mobilised does it become unchangeable (+ "")

cant get it to work though - i want the status to change UNTIl it reached the 09-Mobilised status then to freeze

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this:

=IFERROR(INDEX({Recruit Status}, MATCH([Inductee Name]@row, {Recruit Name}, 0)), INDEX({Arch Status}, MATCH([Inductee Name]@row, {Arch Name}, 0)))

There is no way to get it to return as plain text / no formula without the help of the API, Bridge, or some other third party app, and that also requires not using a column formula which can in turn create its own challenges.

All comments

heyjay

Can you show a sample table of what you want to achieve? Id like to understand more this part,

so when the information requested returned, only when 09-Mobilised does it become unchangeable (+ "")

Susan van Niekerk

In my sheet I am index matching from another sheet called Recruitment - and i am returning the Status from that sheet - BUT when the status changes to 09-Mobilised I want it to change to a text

the reasoning is - when a row in the recruitment sheet changes to 09- Mobilized it moves to the archive sheet and my current sheet looses the status and becomes a "No Match" and I need the status in my current sheet to show when that index/match person is mobilized - i could do it manually and not use column formulas and when it changes to No Match just type over it - but am hoping to automate some of the tasks

Paul Newcome

Are you able to provide some screenshots for context?

Unless you have other instances that could possibly throw that error, you could use an IFERROR.

=IFERROR(current_formula, "09-Mobilised")

Another option would be an IF statement to look at the Archive sheet, and if the person is found there, output the "09-Mobilised", otherwise run your existing formula.

=IF(COUNTIFS(look at archive sheet with range/criteria sets to see if they are there) > 0, "09-Mobilised", current_formula)

Susan van Niekerk

Hey Paul

I had though of a dual formula - i would need it in one long string though

Something like this monstrosity - so need them to look into the Recruit sheet index match return current status, if "no match" returns then do same index match in Arch Sheet and if 09 Mobilized return as a text :-O

=IF(ISNUMBER(SEARCH("09-Mobilised", INDEX({Recruit Status}, MATCH([Inductee Name]@row , {Rec Name})))), INDEX({Recruit Status}, MATCH([Inductee Name]@row , {Rec Name})), INDEX({Recruit Status}, MATCH([Inductee Name]@row , {Rec Name}))) IF NO MATCH, IF(ISNUMBER(SEARCH("09-Mobilised", INDEX({Arch Status}, MATCH([Inductee Name]@row , {Arch Name})))), INDEX({Arch Status}, MATCH([Inductee Name]@row , {Arch Name})) + "", INDEX({Arch Status}, MATCH([Inductee Name]@row , {Arch Name}))) + ""

Paul Newcome

Try something like this:

=IFERROR(INDEX({Recruit Status}, MATCH([Inductee Name]@row, {Recruit Name}, 0)), INDEX({Arch Status}, MATCH([Inductee Name]@row, {Arch Name}, 0)))

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.