Forum Discussion
Getting #Ref when copying or writing the formula in different columns.
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 Nitish98065 If you have access to the lastest Excel (Office 365) you can use the FILTER function to achieve your goal. Demonstrated in the attached file.
If not, consider to restructure your data in Sheet2, as shown in the blue shaded area. The Index/Match will work very well, though, having the reference to AUTO and ACCOUNT in a single cell above the range of your lookup values doesn't make it easier. Better to repeat the label in column A on Sheet1.
I don't believe that a straight-forward Index/Match on your original data in Sheet2 will work as Match only returns the first occurrence. I invite others to think of a better solution in case you must have the data in Sheet2 as is.
Riny_van_Eekelen I can't find the attachment.Can you pls post it again.