QUESTION: =OFFSET(C2:C131,MATCH(C2,C1:F131,1)*1,1,1,1) This formula doesnt seem to work for me and I dont know the syntax to make this work. I am trying to take out multiple values from Multiple Rows and columns.
ANSWER: Hello Mirza,
Can you share with me the exact result you are trying to achieve with this formula. A sample of source date, reference data and the end result you expect. The issue seems to be with the "Match" formula you are using.
---------- FOLLOW-UP ----------
QUESTION: Please see the following data range example. I need to check each entry on the first column against all the rows in the second or third column and in case if there is a duplicate, I want it to extract that value and give it to me. In essence, I want all the values that are in Column A matched with Column B and C and if there is a duplicate, I want all of the ones that are duplicate in a new Column. I hope this helps.
112885 1014126 4010586
4104850 1014126 4105107
4051608 1014303 102958
1012429 1014303 4085443
4165860 1014303 4110999
4073730 1014303 4121658
4026747 1019643 102820
1014303 1019643 4051608
109795 4009621 4206878
2501955 4110999 4149043
4261579 4027559 4110999
4110999 4027559 1009926
4225138 2509641 4001575
4141242 2509641 4107967
4229733 2509641 4097616
4027559 109795 4001623
2509641 109795 4019025
4150252 109795 4064495
4172986 4057044 103065
In the above data, if you see row 11, both 4027559 and 4110999 are appearing in the first column. If you were to place them in a new column, there are two values to be placed in the 11th row of the new column.
Alternatively, if the objective is just to identify duplicates and highlight them, you can achieve the same by using conditional formatting.
You would need to enter the following formula in the conditional formatting dialog box for cell D2: =COUNTIF($C$2:$C$20,D2)>0
Please see the attached picture to see the exact setup.
By using the countif function, we are just asking excel to check if each of the values in column D is also available in column C. If yes, then highlight it red.
If you still need to store these values in separate column, then we could try to create a macro to do the job.
Hope this helps.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here