Forum Discussion

Nitish98065's avatar
Nitish98065
Copper Contributor
Mar 09, 2020

Getting #Ref when copying or writing the formula in different columns.

I am using the below formula.It is working for one column.When I am copying and pasting or either writing the similar formula it is giving #Ref!.

 

Column 1 Formula Working : =INDEX('Data from Extract for DPS'!$C$2:$C$66,MATCH('RCCS WBR Summary Screening'!$C$16,'Data from Extract for DPS'!$A$2:$A$66,0),MATCH('RCCS WBR Summary Screening'!D18,'Data from Extract for DPS'!$B$2:$B$66,0))

 

Column 2  Formula Not Working : =INDEX('Data from Extract for DPS'!$C$2:$C$66,MATCH('RCCS WBR Summary Screening'!$C$16,'Data from Extract for DPS'!$A$2:$A$66,0),MATCH('RCCS WBR Summary Screening'!D19,'Data from Extract for DPS'!$B$2:$B$66,0))

 

Note only D18 & D19 changes within the 2 columns.

 

Thanks

Excel on Mac

8 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 09, 2020

    Nitish98065 The range you are indexing (C2:C66) is 65 rows high by 1 column wide. Then you base the column and row reference on the two MATCH functions. If either of these return a row number less than greater than 65 or a column number anything else but 1, the result will be #REF!. If there is no match, yu will get #NA!

    I suspect that the value in cell D18 equals the first element of the range B2:B66. So, the match will return 1. And I guess that D19 does not! The column index in a one-column range may only be 1. It's meaningless to calculate it with a MATCH from a range with 65 elements.

    • Nitish98065's avatar
      Nitish98065
      Copper Contributor
      Mar 09, 2020

      Riny_van_Eekelen Thanks for your quick reply. I changed my formula with short ranges and it started working as I am giving fix range.Now my concern is if my data is not sorted and I don't know the range of data in another sheet how it will work.

       

      Modified Formula Working: =INDEX('Data from Extract for DPS'!$C$2:$C$5,MATCH($D17,'Data from Extract for DPS'!$B$2:$B$5,0),MATCH($C$16,'Data from Extract for DPS'!$A$2:$A$5,0),1).

       

      I am trying to check combination of two values from tab 1 in tab 2 and populating its value in tab1.Is there any other way I can make my formula.

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Mar 09, 2020

        Nitish98065 A match of type 0 (zero) is an exact match. The help section on MATCH says:

        "MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order."

         

        If you don't know the "size" of the range in the other sheet, but you do know that it is in column B, you may use "$B:$B" as a reference. This will take the entire column B into account.

         

        But, it would be a lot easier if you could upload your example so that I can see what you actually are trying to do.

         

Resources