To Index & match a name with a number

    I am looking for a index and match or maybe a vlookup formula to return certain values.


    In col Z14:Z25 I have a list of names and alongside in col Y14:Z25 numbers are listed from 1 to 12, the names can change from time to time but the numbers will be constant. In col D49:D71 (each of 2 cells are merged) and the same names as in col Z14 appear in these cells although due to ranking the order will vary.


    In cell AA49 in col AA49:AA72 I would like the name that appears in the opposite col to match but as a number. I attach a file which hopefully will explain it better and hope someone can help or is there an easier way.


    Thanking you in anticipation.

    Re: To Index & match a name with a number


    Try:


    =INDEX($Y$14:$Y$25,MATCH(C49,$Z$14:$Z$25,0))


    or


    =SUMIF($Z$14:$Z$25,C49,$Y$14:$Y$25)


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: To Index & match a name with a number


    Yes I had the same as your first formula and it didn't seem to work but the second one did so very many thanks.

Participate now!

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

Register Yourself Login