IFERROR

[Akie]

Hi there,
I've recently started a new job populating a roster spreadsheet.
(Excel 2016)

This code formulated is currently in Row 10:
(This would be the code in I10)

=IFERROR(INDEX($D$13:$D$98,MATCH("N MAN",I$13:I$98,0)),"")

A list of staff names D13 to D98 & I13 to I98 is job roles.
There is 1 "N MAN" so whoever i roster as N MAN it populates their name in row 10 for that day.

Sometimes there will be an SN MAN instead of N MAN and i want it to search for both and populate the name for which ever one occurs to Row 10, but now sure how to formulate this on the current formula.

Many Thanks

 

[maabadi]

Welcome to MrExcel Message Board.
You Can Change it to this formula. But this formula only Show first finding Value at range

=IFERROR(INDEX($D$13:$D$98,MATCH("*N MAN",I$13:I$98,0)),"")

Or Write your Criteria at One Cell and Replace "N MAN" With Cell Address in formula.

 

[Akie]

Welcome to MrExcel Message Board.
You Can Change it to this formula. But this formula only Show first finding Value at range

=IFERROR(INDEX($D$13:$D$98,MATCH("*N MAN",I$13:I$98,0)),"")

Or Write your Criteria at One Cell and Replace "N MAN" With Cell Address in formula.

Thank you, this is now populating the name with the *
The only issue i have now is i have an FN MAN which i don't want to include with the *

Can i add to the formula with *N MAN but not count FN MAN?

Many thank again.

 

[maabadi]

Try this:

=IFERROR(INDEX($D$13:$D$98,MATCH(1,INDEX(("N Man"=I$13:I$98)+("SN Man"=I$13:I$98),0,1),0)),"")

 

[Akie]

Thank you very much for your help thats worked

 

[maabadi]

You're welcome & Thanks for feedback.