#Invalid Ref error in a report, but not the source

01/12/22
Answered - Pending Review

I'm seeing an invalid reference error in a report on a dashboard, but the source data is good and the vlookup is pulling in the data I want to see.


Any thoughts on the cause/solution? I've narrowed down, deleted and re-created the cross sheet reference several times and the error persists.

Screenshots of the error and data source are below.


Answers

  • Hi @Jason_W

    I'm unable to re-create what you're seeing with a VLOOKUP; if the source data shows correctly then it should pull through to the Report. However I wonder if the reference range is getting too large to calculate.

    Can you try swapping out your VLOOKUP formula for an INDEX(MATCH instead? This will evaluate the two columns separately without including any cells between them.

    An INDEX(MATCH works like this:

    =INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))


    Let me know if selecting two smaller ranges instead of one lookup table has helped get rid of this issue!

    Cheers,

    Genevieve

Sign In or Register to comment.