1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Display Value from Cells Down Column Based Off Existence

Discussion in 'Ask an Excel Question' started by Adam1245, May 1, 2017.

  1. Adam1245

    Adam1245 New Member

    Messages:
    5
    Hello,

    I have a column with cells containing the same concatenate formula. I've set a condition with these formulas where concatenate will not occur unless "Best" shows in the column to the left.

    I want to be able to display the only concatenate result (which could appear in any of the 300 rows found in the column) in one of my cells.

    I would like to stick away from macros or VBA if possible.


    Thanks!
  2. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,223
    Upload a sample file and the desired results.

    Regards
  3. Adam1245

    Adam1245 New Member

    Messages:
    5
    Hello,

    See attached for what I've started. As you can see cell I3 is the result of the site that is closest to the Lat and Lon placed in B3 and C3. Sites are already preloaded so that as the tool is used different coordinates will be entered to find the closest site. Column P will only show a result if it is the best/closest of all sites. The problem in this scenario is trying to get I3 to only show whatever appears in any of the rows down column P.

    Let me know if you have any questions.


    Thanks!

    Attached Files:

  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,223
    In I3 :

    =INDEX(P$7: P$1000,MATCH(1,INDEX(0+(P$7: P$1000<>""),0),0))

    Regards
    Thomas Kuriakose likes this.
  5. Adam1245

    Adam1245 New Member

    Messages:
    5
    Wow! Perfect!

Share This Page