Index and Match function is not working

Hanuman Veda

Hi Team,

Am trying to create a sheet using Index and Match function and am able to fix the issue.

Hanuman Veda

Hi Jeff,

The below formula worked and data is showing correctly..

=IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, "Approved", {ADMIN ONLY Intake Master Range 6}, "Approved", {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1), "")

Thank you for the support

Thank you,

Hanuman Veda

Jeff Reisman

@Hanuman Veda What you tried got you the #UNPARSEABLE error because the syntax is incorrect... the system is trying to find the [Ideation Phase] column that doesn't exist in the sheet where the formula is! Also, you didn't try the formula I gave you, because this:

=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3},[Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR([Ideation Phase]@row = "New", Ideation Phase]@row = "Triage", Ideation Phase]@row = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4},"Not Started")),1)

is not equal to mine:

=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started")), 1)

Just paste the above formula directly into your sheet. As long as {ADMIN ONLY Intake Master Range 5} is referencing the Ideation Phase column in the source sheet, this will work. The "@cell" tells Smartsheet to look through the entire column referenced by {ADMIN ONLY Intake Master Range 5} to find cells equal to New, or Triage, or Portfolio Review.

Jeff Reisman

@Hanuman Veda Details matter with formulas. A syntax mistake like an extra parentheses will mess the whole thing up. I walked you through it in my last post! You left the extra parentheses in there, the very one I indicated need to be removed.

I'm trying to both give you the formula that will work and show you how the formula works and how to troubleshoot. I don't know how else to help you. Select the text of the formula below, then hit Ctrl-C, and then Ctrl-V over your existing formula . Try one more time:

=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @Cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1)

Hanuman Veda

Hi Jeff,

Thank you for the support !!

I just added IFERROR at the start of the formula and ,"") at the end. Now the formula is working with the expected results. Thank you a lot.

=IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1), "")

Thank you ,

Hanuman Veda

Jeff Reisman

@Hanuman Veda We would love to help you with this, but we don't have enough information.

What formula are you trying to use? What error message are you getting? What types of data and columns are involved? Is this local to one sheet or are you referencing ranges on another sheet?

INDEX function page

MATCH function page

Hanuman Veda

Hi Jeff,

I want to write a formula that when "Ideation Phase' is Approved, "Intake Phase" is Approved and "Project Phase" = Not Started then "Project Name" should be populated is your answer. What an I doing wrong?

=IF(AND({Kanban Range 5} = "Approved", {Kanban Range 6} = "Approved", {Kanban Range 4} = "Not Started"), "{Kanban Range 1}, "NA")

When i used the above formula promoted with #UNPARSEABLE

Source File.

Jeff Reisman

@Hanuman Veda You'll probably want to use an INDEX/COLLECT there instead.

=IFERROR(INDEX(COLLECT({Kanban Range 1}, {Project ID Range}, [Project ID]@row, {Kanban Range 5}, "Approved", {Kanban Range 6}, "Approved", {Kanban Range 4}, "Not Started"), 1), "")

The logic is: Give me the cell value from Kanban Range 1 (Project name column) from the row where Project ID = this Project ID, and where {Kanban Range 5} = "Approved", {Kanban Range 6} = "Approved", and {Kanban Range 4} = "Not Started". If there's no row that matches those criteria, leave the cell blank.

Hanuman Veda

Hi Jeff,

I tried by using the formula which you shared as below. Its also populating the data wherein Ideation Phase or Intake phase status are "Portfolio Review, Not Started, New Initiative, Triage, Cancelled"

I need the data only when Ideation Phase = Approved, Intake Phase = Approved and Project Phase = Not Started

=IFERROR(INDEX(COLLECT({Kanban Range 1}, {Project ID Range}, [Project ID]@row, {Kanban Range 5}, "Approved", {Kanban Range 6}, "Approved", {Kanban Range 4}, "Not Started"), 1), "")

Kanban Range 1 = Project Name

Kanban Range 3 = Project ID

Kanban Range 5 = Ideation Phase

Kanban Range 6 = Intake Phase

Kanban Range 4 = Project Phase

Note: Am attaching the data which got populated and highlighting the fields for your reference.

SS Formula Data.xlsx

If required, Please send me the meeting invite on Zoom or Teams

hanuman_p_veda@optum.com Phone Number - +916301919390

Thank you,

Hanuman Veda

Jeff Reisman

@Hanuman Veda

Put the Kanban Range 1 range in place of Project ID Range:

If that still doesn't work, send a screenshot of the formula as it appears in Smartsheet (with the color-coded parentheses, etc., like this:

Hanuman Veda

Hi Jeff,

I have replace Kanban Range 1 range in place of Project ID Range: Now its showing blank (I mean data is showing blank)

Sorry, i tried to send a screenshot with the Color-coded but am unable to color the formula. Am very sorry

Jeff Reisman

Take the IFERROR away from around the formula (including the , "") at the end, so you can see what error you're getting.

Best practice is to leave IFERROR off until you know the formula works 100% the way you want it to.

Hanuman Veda

Hi Jeff,

If i remove IFERROR away from around the formula (including the ,"") at the end, am getting #INVALID VALUE.

Hanuman

Hanuman Veda

Hi Jeff,

The below formula worked and data is showing correctly..

=IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, "Approved", {ADMIN ONLY Intake Master Range 6}, "Approved", {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1), "")

Thank you for the support

Thank you,

Hanuman Veda

Hanuman Veda

Hi Jeff,

Please suggest a formula i need to use when "Ideation Phase' is Triage or Portfolio Review or New and "Project Phase" is Not Started then "Project Name" should be populated is your answer.

Source Data

Thank you,

Hanuman

Hanuman Veda

I tried using the below formula prompted with #INCORRECT ARGUMENT SET

=IF(OR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, "Triage", {ADMIN ONLY Intake Master Range 5}, "Portfolio Review"), 1), ""))

Hanuman Veda

I tried the below formula also. prompted with #INCORRECT ARGUMENT SET

=IF(OR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, "New", {ADMIN ONLY Intake Master Range 5}, "Triage", {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1), ""))

Jeff Reisman

@Hanuman Veda

Try this first:

=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started")), 1)

You'll get #INVALID VALUE on the rows that don't match the criteria. This is expected behavior. You should get the Project Name value returned on rows where the criteria DO match. Once you are satisfied that you get the Project Name when the criteria match, and #INVALID VALUE when the criteria don't match, then add the IFERROR on to suppress the error message:

=IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started")), 1), "")

Hanuman Veda

Hi Jeff,

Thank you for the support !!

I tried both the formulas and received an error as #UNPARSEABLE

=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3},[Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR([Ideation Phase]@row = "New", Ideation Phase]@row = "Triage", Ideation Phase]@row = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4},"Not Started")),1)

=IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3},[Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR([Ideation Phase]@row = "New", Ideation Phase]@row = "Triage", Ideation Phase]@row = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4},"Not Started")),1),"")

.

Jeff Reisman

@Hanuman Veda What you tried got you the #UNPARSEABLE error because the syntax is incorrect... the system is trying to find the [Ideation Phase] column that doesn't exist in the sheet where the formula is! Also, you didn't try the formula I gave you, because this:

=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3},[Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR([Ideation Phase]@row = "New", Ideation Phase]@row = "Triage", Ideation Phase]@row = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4},"Not Started")),1)

is not equal to mine:

=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started")), 1)

Just paste the above formula directly into your sheet. As long as {ADMIN ONLY Intake Master Range 5} is referencing the Ideation Phase column in the source sheet, this will work. The "@cell" tells Smartsheet to look through the entire column referenced by {ADMIN ONLY Intake Master Range 5} to find cells equal to New, or Triage, or Portfolio Review.