Excel/Formula Check


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

Conditional formatting to identify duplicates
Conditional formatting  
Hi Mirza,

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.

About Excel
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


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2017 About.com. All rights reserved.