Forum Discussion
Getting #Ref when copying or writing the formula in different columns.
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.
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.