Results 1 to 10 of 10

Thread: Index match formula [Solved]

  1. #1

    Index match formula [Solved]



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 2nd 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.
    Attached Files Attached Files
    Last edited by NBVC; 2013-12-12 at 03:54 PM. Reason: Replaced confidential file with random file

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    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.
    Last edited by NBVC; 2013-12-09 at 04:07 PM.


  3. #3
    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

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    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.
    Attached Files Attached Files
    Last edited by NBVC; 2013-12-12 at 03:50 PM. Reason: Replaced confidential file with random file


  5. #5
    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.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    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.


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

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    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.
    Last edited by NBVC; 2013-12-12 at 03:55 PM. Reason: typos


  9. #9
    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.
    Attached Files Attached Files

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    Ok, thanks. I replaced the files for you. Please remember for next time to use mockups and not original confidential data.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •