Index search

[AndyPandy]

Hi

This is the best way i can describe this so if now one understands then I apologise.

I am currently using the index match forumula to search my spreadsheet and pull up data but after testing I have noticed the following problem,

The covering engineer coloum will sometime show that the covering engineer is down to cover more than one area but will on dispaly the one area on the search form is there anyway around this, in the attached document i have highlight said problem.

idealy would need addtional fields under the search form to show if the covering engineer is covering another area. currently we have to rely on there being an eningineer in the engineer coloum.

 

[NBVC]

I would list them in a table format like the original,

So, first add a helper column that finds and counts the matches.

In G5 enter formula:

=IF(OR(A5=$J$3,E5=$J$3),COUNT(G$4:G4)+1,"")

copied down.

Then, say in K6 enter formula:

=IFERROR(INDEX(A:A,MATCH(ROWS($K$6:$K6),$G:$G,0)),"")

copied down as far as you think you will ever have matches for, then copy across to column P.

You will probably need to redesign your Search form though, to allow for the multiple results.