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.
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.
Tab1 Tab2 AUTO Values Required AUTO JON 1800 ToM AUTO ToM 1200 RAM AUTO RAM 1300 HARI AUTO HARI 1400 MAT AUTO MAT 1500 ACCOUNT ACCOUNT ToM 1501 ToM ACCOUNT RAM 1502 RAM ACCOUNT HARI 1503 HARI ACCOUNT MAT 1504 MAT ACCOUNT Jon 1505
