1. D

    Dragging Index Formula down rows and changing the return column

    I have a formula that I am using (after you paste it press "Enter+Ctrl+Shift" to run it as it has an array inside of it): =INDEX('[Database_IRR 200-2S.xlsm]HE 171'!$F:$F,SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]HE 171'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]HE...
  2. U

    Treeview Parents/Child - How to return the first non blank value located one column to the left of the source cell and n rows upward.

    Hi all, I'll try to be as clear as possible so please do not hesitate to ask for clarification. I'm working with a text file data extracted from CATIA V5 that replicates the tree structure of an assembly with its components and subcomponents. From that text file I arranged the source data in...
  3. C

    Extract Values that meet criteria

    Hello All, I am trying to extract some data from one sheet into another based on two different criteria. I need to select the data from the last six months and where column C = 1. So far I have been using an Index and small function. I have been able to extract some data, but not the correct...
  4. H

    Multiple match criteria in different columns to return the unique value

    Hi, Can somebody help me with this problem? Thanks, Bobby
  5. I

    Formula for identify related cells based on a common criteria

    Hello everyone I am looking to identify family members who are eligible for a benefit. Let's say govt. has identified a set of eligible fruits that qualify under benefits. If any family member sells one of those fruits, all family members become eligible for benefits. All family members are...
  6. A

    How to use index and arrays for a nested query?

    Hi, I have a table as follows: I am trying to return the latest date for a given Fruit and Type - can anyone help. i.e. Pear with a Type of AI_646 should return a date of 10/10/2020. Can anyone help. I have to use my work version of Excel which is Office 2010, so I can't use the latest...
  7. X

    INDEX/MATCH within an Array

    Hi I have a large report that I am pulling into a multi-dimensional array. I'm then using a function to remove dupes and then running various loops summarizing the data from the main array, but I am stuck on one point. I can't find a way to use INDEX/MATCH functions within arrays. ie if I was...
  8. HawaiianShirts

    Index Match with Constant Row and Variable Columns

    I'm creating a worksheet (Data) that will reference another (Sheet1). In Sheet1, the data will change periodically by being pasted in from a form filled out by someone else. The trouble is that the form has three versions, and each version has a slightly different number of columns. I need to...
  9. S

    Cell Reference Named Ranges as Arrays

    Hi, In an excel file we have various named ranges. Ideally in a column we could reference these as arrays in an INDEX formula as a name so the array would change depending on cells in a column, just like how the row or column reference numbers can change. Do you know if this is possible as...
  10. T

    Improve formula for dynamics list unique values based on two criteria

    I want to improve the formula for extracting unique zip code for 2019. I am currently using this formula but its very slowly: =IFERROR(INDEX('Project Data'!E:E;MATCH(0;COUNTIF('Dashboard Sales'!G114:$G$114;'Project Data'!E:E)+IF('Project Data'!L:L>=$E$113;1;0)+IF('Project...
  11. B

    Partial match when look up value is lengthier than corresponding value in a table or a range

    I have table as below. In first column name of the company and in next column corresponding manager Name. Company Name Manager Name Amcor Amol Thermax Sagar The look up value of company name is not exact match in this case. e.g. It is "Thermax Ltd" The company name from table is a...
  12. M


    Hi everyone, Last time I reached out to you, I felt the love with so many responses so quickly. I am a teacher and I’m trying to look for ways to streamline my duties. I'm currently reviewing the courses I teach - focussing on equipment used, and duration for each module. I have been asked...
  13. M

    COUNTIF INDEX type issue. Producing a summary table..

    Hi folks, After success on this board earlier this week, I’ve decided to put together a management information type system for my department at school. I currently teach at different sites and I’m trying to ensure an even split of equipment between sites. I’ve put a simple site, course and...
  14. M

    Help with index match sumif and more.

    Hi there, I’m a teacher who is looking to prepare better for the academic year. I teach a range of courses which require different amounts of time to setup and I’m keen to get a better understanding of the set up times for each course I teach. I have attempted todo this is a matrix format, but...
  15. X

    INDEX and MATCH through 2 Sheets.

    I am having a complete mental shutdown right now. Basically I'm trying to get match&index to look at sheet 1's SKU and match with Sheet 2's SKU to display the QTY from sheet 2, int Sheet 1's (DISPLAY Column) I got it to work using only 1 sheet with the formula =INDEX('magento...
  16. F

    What does the combination of Index and Match do?

    Hi all could someone explain what is this line of formula doing in Excel ? INDEX($C$26:$W$39,A27,MATCH(Fmax,Cf,1))
  17. L

    Index match, xlookup or both?

    Hi, I'm trying to find a way to return a value based on an index match formula, if the returned value is 0 then move up until there is a number. Column look up: A Row look up: 4 A B C D E F 1 0.34 0.36 0.24 0.75 0.37 0.12 2 0.15 0.74 0.35 0.01...
  18. A

    Pulling List of Rows from other sheet dependent on matching Cell Value? HELP See Pictures!

    Hello, I have a workbook with Sheets 1 and 2 with data on them. This template is on Sheet 3. The template uses the cell D5 to pull data from these different sheets to populate. You type a project number (Proj#) in D5 and the rest should populate. On "Sheet 1", I have a listing of all the...
  19. E

    Index & Match functions in VBA

    Hello guys, I'm trying to create a small VBA script but i m stuck with the index / match functions. I have this error: "Compile error: Object required" for the Match I'm pasting my code below: ' Define params for index and Match functions Set ws.Release =...
  20. G

    formula to match next value if value is in row above.

    I have this formula, I am running into an issue when I want the nth value, but it matches the nth value for another column, then does a match to get what value in this column. Visual is below. Column B matches the index of Column C to return a value based on the value. I want it to show what the...

Some videos you may like

This Week's Hot Topics