Index match formula [Solved]

[JOONA]

Hi I hope someone can finally solve this for meas I have ran out of ideas.

I have the following spread sheet which has asearch form attached,

If I enter an engineer number in it from theengineer column it will search the fields and show me if that eng is in and ifnot who is covering. However I have x2 problems, 1 is that on certain resultsit returns 0 if there is no info instead of NA. so how can this be addressed.

And the 2^nd problem is as follows.

In the covering engineer column. Some engineerscan cover more than one engineer see example engineer 7106 you can see that hecovers 2 areas. What I would like to happen is a formula that I can enter intoa cell to show the following.

If I entered 50007106 it would still show thatthe original area eng 7112 and 7106 is covering but I need it to show the otherarea that 7106 also covers which is 50007384.

Hope this make sense.

So ideally it would match what is in thecovering eng column and return the values or value in the covering eng columnas well as comments etc. can this be done. I have tried look up but found theindex match side of things better as it looks left and right where by vlook updoes not.

[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.

[JOONA]

Hi

Thanks for that but is there any chance you could show me by what you mean and how would i re-do the search form, your help would be greatly apreciated. thanks

[NBVC]

See attached.

Note: I noticed that in some Covering Engineer cells, you have multiple id's listed, so I updated the Helper column formula to find the number within the cell....

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

copied down..

I also revised your form, but you can play around with the design as you wish to get the info you need. The main thing is that it all becomes tabular, instead of scattered cells.

[JOONA]

Thank you that works, fine. However lol is the an adjustment that can be made as i have noticed that when there is no eng number entered in the search box and you press enter it displays a load of info, i only need it to display the infomration once an engineer number has been entered, if not then not to worry.

[NBVC]

Change the formula in K6 of the Fire Checklist sheet to:

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

copied across and down.

[JOONA]

Thanks that worls fine. if possible and if you a moderator could you now delete these threads. or at least the attachments. thanks

[NBVC]

I would prefer that nothing gets deleted. When posting to a forum, you should understand that it means the information is public. If you are going to post samples, you should take care not to post confidential information. These forums are here to assist you and maintain a database, so that others can benefit when they have similar problems and they want to do a search for a solution before asking the questions.

The best that I can offer you is, that you amend the attachments to show edited/changed data, then send them to me through this thread. Then I can replace the attachments in the thread with your samples. As long as the solution still applies without hiccups.

[JOONA]

Ok thanks i have done a smaller spread sheet with sample data to use in place of what is already attached I have tested the sample data and it still works

Once you have ammended the thread attachments please mark has solved.

[NBVC]

Ok, thanks. I replaced the files for you. Please remember for next time to use mockups and not original confidential data.