Match formula: input data range based on cell value

  • Hi.
    In regards to the formula =ADDRESS(MATCH(M13,'Sheet1'!K1:K23,0),11)


    I would like the value of K1 in the above formula to equal the cell value of cell G13 in Sheet2. If it helps, the cell value on cell G13 in Sheet2 is $K$6.
    Thanks.

  • Try:


    =ADDRESS(MATCH(M13,INDIRECT("'Sheet1'!"&G13):K23,0),11)

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

  • I get #Value - A value used in the formula is of the wrong data type.
    However, after playing around with some numbers and using "'Sheet2'!" in the formula, I get an answer. So don't know what the problem is with using "'Sheet1'!".

  • Perhaps try it like this...


    =ADDRESS(MATCH(M13,INDIRECT("'Sheet1'!"&G13):INDIRECT("'Sheet1'!K23"),0),11)

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

Participate now!

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