1. J

    Countif using index and match

    Hi, I am trying to extract the numbers against each names from the list. I tried using index-match-countif (in E4:E7) but couldn't add the name criteria (E2) to get the result I am looking for (as shown in G4:G7) Sorry if I am not explaining it clearly. Your help will be much appreciated...
  2. S

    Find nearest and exclude member of array

    Hello! I need help with {=INDEX(MATCH.....} or something like that formula. I would like to make a formula that finds the nearest value, of a given cell, in an array of data, and exclude that array member in folowing cell. Here is an example: Thank you in advance!
  3. I

    Index and Match on multiple similar tables

    Hello, I need to use Index and Match function for this problem. Need to select the specific table using the first parameter and then a value inside the table by using two other parameters. eg Need to find Shipping per unit using the tables below
  4. B

    Dynamic Column Name Identifier Formula (USING MOST RECENT VERSION OF OFFICE 365)

    File: Dynamic Column Identifier Formula.xlsm I created this formula to dynamically return the column name based on the corresponding column number. That said, it works intermittently once you get to Column AA. I could understand if it was broken completely at Column AA and beyond, but I cannot...
  5. S

    Index and Match

    Hi, i have this employee spreadsheet where i need to find the performance potential based on several criteria i.e. performance rating, attitude and competency. No Employee Name Performance Rating Attitude Competencies Performance Potential 1 MARCUS LIM A 3 1 ? 2 ELIZABETH TAYLOR A+ 1 2...
  6. S

    Dynamic Index Array

    Hi, I'm not sure if this is possible so would appreciate any responses. I'd like to be able to use a named range in an index but would like the named range to be dynamic. For example ideally the formula would be like =index(cell reference to dynamic array, etc). The problem is I don't...
  7. C

    VLOOKUP OR INDEX? Duplicate values exist, keep non-blank value (text & numbers)

    I have a very large spreadsheet that I am trying to return the value of some columns based on matching depths. Tab1 of my spreadsheet has no duplicate depths while Tab2 DOES have duplicate depths, some of which the columns have null values. The attached Example Tab1 image has a yellow...
  8. P

    Problem with populate listbox using Application.Index

    Hi everybody. I have a problem with a population of a listbox using the method Application.index. I need load a listbox with a series of data in a sheet. I only need just some data (some rows) of the sheet. This data is filtered by a intelligent search that indicate which row should be load in...
  9. E

    Check two columns value and add them into Rows

    Hi All, I need to check the values in multiple columns and if the data is there then need to add them in Rows in Diff sheet. Below is the example data set and expected. I have tried with Index & match still not able to get the expected result. Any suggestions will be helpful. Thanks kal
  10. P

    Formula to determine if a date and time are within a range.

    Hello, Completely stumped here. Thought I would take a change and add some experts. I need to be able to reference a table that is attached as an image. I need a formula that can read a cell that says "44027 11:00" find where it is on the reference table and return the code associated with...
  11. dreen

    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...
  12. 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...
  13. 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...
  14. H

    Multiple match criteria in different columns to return the unique value

    Hi, Can somebody help me with this problem? Thanks, Bobby
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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...

Some videos you may like

This Week's Hot Topics