Get row number(s) of a match

  • Dear Experts ,


    I have a excel table with many values , I want the SIMPLEST FORMULA to list the row number(s) of this match.
    [TABLE="width: 192"]

    [tr]


    [TD="width: 64"]Row
    [/TD]
    [TD="width: 64"]A[/TD]
    [TD="width: 64"]B[/TD]

    [/tr]


    [tr]


    [TD="align: right"]1
    [/TD]

    [td]

    Name

    [/td]


    [td]

    Age

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]2[/TD]

    [td]

    A

    [/td]


    [TD="align: right"]15[/TD]

    [/tr]


    [tr]


    [TD="align: right"]3[/TD]

    [td]

    B

    [/td]


    [TD="align: right"]78[/TD]

    [/tr]


    [tr]


    [TD="align: right"]4[/TD]

    [td]

    C

    [/td]


    [TD="align: right"]13[/TD]

    [/tr]


    [tr]


    [TD="align: right"]5[/TD]

    [td]

    A

    [/td]


    [TD="align: right"]51[/TD]

    [/tr]


    [tr]


    [TD="align: right"]6[/TD]

    [td]

    D

    [/td]


    [TD="align: right"]13[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7[/TD]

    [td]

    A

    [/td]


    [TD="align: right"]8[/TD]

    [/tr]


    [tr]


    [TD="align: right"]8[/TD]

    [td]

    K

    [/td]


    [TD="align: right"]13[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    value

    [/td]


    [td]

    A

    [/td]


    [TD="align: right"]2[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"]5[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"]7[/TD]

    [/tr]


    [/TABLE]


    Thanks,

  • Re: Get row number(s) of a match


    hey there,


    This will do what you want.


    =MATCH(A20,A2:A15,0)+ROW(A2:A15)-1


    It assumes your lookup value is in A20. Place formula in B20

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!