Help with IF(Index(Match Formula

Mkaur

Hi,

I need help with writing out IF(index(Match formula. Originally, I was pulling data from specific field and adding to my new sheet. I utilized the below formula to pull data. However, now i have two additional column (State, and LOB) that I need the data to filter by.

=Index({TEST01 Project ... Range Comments},Match(Task@row,{TEST01 Project ... Range Task Name},0),1)

Basically, what I am trying to achieve is if state is X and LOB is Y then index match the task name to comment.

Paul Newcome

You would need an INDEX/COLLECT instead.

=INDEX(COLLECT({Range To Pull Over}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria, {3rd Criteria Range}, 3rd criteria), 1)

Mkaur

@Paul Newcome I tried the formula you suggested, but I am getting #Unparseable error. Below is what I entered, not sure what i am doing wrong.

=INDEX(COLLECT({Test01 Project… Range Comment}, {Test01 Project…Range 1}, WA,{Test01 Project…Range 2}, LOB 1,{Test01 Project…Range Task Name} (Task@row),1)

I am pulling the date from Test01 Project sheet and bringing over the values in the comment section of that sheet with stipulation of what state and LOB it is.

Paul Newcome

You need a comma between the third range and criteria set as well as removing the opening parenthesis before Task@row.