Page 1 of 2 12 LastLast
Results 1 to 10 of 17

find first instance, diplay value from another column

This is a discussion on find first instance, diplay value from another column within the Excel Questions forums, part of the Question Forums category; In col A, I have the dates. I would like to create a formula in all the other columns in ...

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Location
    Ridgetown Ontario Canada
    Posts
    190

    Default find first instance, diplay value from another column

    In col A, I have the dates. I would like to create a formula in all the other columns in row 1 that will look fror the first instance of a value in each column and return the date that that threshold value is reached.

    The value I am looking for is 3, but the values in the columns might look like 0 1 1 1 3 2 1 3 4 2 0 etc. I want to return the date of the FIRST instance of 3. How would I do this?
    Thanks
    Rick

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330

    Default Re: find first instance, diplay value from another column

    Have you tried VLOOKUP?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    Aug 2002
    Location
    Ridgetown Ontario Canada
    Posts
    190

    Default Re: find first instance, diplay value from another column

    I haven't used VLOOKUP for something like this before. I can't see how it would do what I need.

    How would I return the date of the FIRST instance only?
    I want this date to be displayed in a single cell at the top of the column.

    Rick

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,895

    Default Re: find first instance, diplay value from another column

    Quote Originally Posted by ridgetown_rick
    I haven't used VLOOKUP for something like this before. I can't see how it would do what I need.

    How would I return the date of the FIRST instance only?
    I want this date to be displayed in a single cell at the top of the column.

    Rick
    Are you looking for something like this?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book7___Running: xl2000 : OS = Windows NT 4
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    1
    *21-12-0317-2-0313-2-03*
    2
    DateT1T2T3*
    3
    13-2-03123*
    4
    17-6-03013*
    5
    17-7-03111*
    6
    16-10-03100*
    7
    17-2-03130*
    8
    27-11-03133*
    9
    19-4-03011*
    10
    23-6-03232*
    11
    19-12-03022*
    12
    21-12-03332*
    13
    9-8-03032*
    14
    13-6-03233*
    15
    15-9-03112*
    16
    2-11-03223*
    17
    *****
    Sheet1*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The formula in B1 is...

    =INDEX($A$3:$A$16,MATCH(3,B$3:B$16,0))

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Location
    Ridgetown Ontario Canada
    Posts
    190

    Default Re: find first instance, diplay value from another column

    That is sooooo perfect - you can't imagine the grin across my face right now.

    Thank you!!!

  6. #6
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Default Re: find first instance, diplay value from another column

    sorry i dont mean to hijack this thread, however i have need of similar expertise. the variation to my question is i want to look for the first instances of a particular value within groupings. for eg. i want to find the first time a person's monthly salary reached a set figure and return the date in a separate column.


    Name Date Salary Result
    John Doe 1/1/10 450 N/A
    John Doe 2/1/10 500 2/1/10
    john Doe 3/1/10 500 N/A
    Mary Doe 11/1/09 380 N/A
    Mary Doe 12/1/09 380 N/A
    Mary Doe 1/1/10 500 1/1/10

    would really appreciate the hep thanks

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,895

    Default Re: find first instance, diplay value from another column

    Quote Originally Posted by kenatu View Post
    sorry i dont mean to hijack this thread, however i have need of similar expertise. the variation to my question is i want to look for the first instances of a particular value within groupings. for eg. i want to find the first time a person's monthly salary reached a set figure and return the date in a separate column.


    Name Date Salary Result
    John Doe 1/1/10 450 N/A
    John Doe 2/1/10 500 2/1/10
    john Doe 3/1/10 500 N/A
    Mary Doe 11/1/09 380 N/A
    Mary Doe 12/1/09 380 N/A
    Mary Doe 1/1/10 500 1/1/10

    would really appreciate the hep thanks
    Is the max value the set figure?
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Default Re: find first instance, diplay value from another column

    the excrcise is really to determine when a person has reached the top of their pay grade. so the set value would be the max value for group of individuals in the same scale.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,895

    Default Re: find first instance, diplay value from another column

    Quote Originally Posted by kenatu View Post
    the excrcise is really to determine when a person has reached the top of their pay grade. so the set value would be the max value for group of individuals in the same scale.
    D2, control+shift+enter, not just enter, and copy down:
    Code:
    =IF(MATCH(MAX(IF($A$2:$A$7=A2,$C$2:$C$7)),
        IF($A$2:$A$7=A2,$C$2:$C$7),0)=ROW()-ROW($C$2)+1,$B2,"N/A")
    Assuming too much and qualifying too much are two faces of the same problem.

  10. #10
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Smile Re: find first instance, diplay value from another column

    thank you for your very prompt response. I am tryin to get this to work however; i need to be able to search for the top of the salary scale as the figure may not be in the table. it is possible that nobody has reached the top of their pay scale so its not necessarily the thier most recent largest payment. could you tel me how to modify this to work like that? where i put the top in the code and then ask it to find the first instnaces of that inputted figure?

    much appreciated.

    Thanks

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com