index & match

  1. A

    Excel to return a value in a column, if multiple criteria meets

    I tried to return a value in sheet1, column A (vertical), to sheet 2 (Horizontal format), if the "Process_Step" and "WO" is matched. As the data is huge and there're many columns, I do not know how to specifically to use the index and match or vlookup, to return the column A value (Date) if the...
  2. D

    Vlookup & Offset #Value Error

    I have two workbooks, one where I am doing the Vlookup and the other contains the table I need to retrieve the information from. I am trying to basically Vlookup the table found in the other workbook '[Database_IRR 200-2S.xlsm]Changes'! below row 3 as sometimes the lookup retrieves information...
  3. M

    Using Index/Match or Vlookup to get one result out of multiple results

    Hi there. My very first post here. I'm an intermediate Excel user and have been working on a project. I have a problem where I am using Index and Match formula to index one result (Using Match formula to determine both the row number and the column number in the Index formula). The problem is...
  4. 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...
  5. J

    Using Vlookup and Index/Match across differing sheets

    So I have one table with differing columns of ID and its information per row on 1 sheet and another sheet has ID that asks for associated data from different columns. Eg ID is A column and info for associated is in D,E or B column. Each row has unique information. On another sheet is ID and...
  6. X

    How to use index match to calculate inventory transfer

    Hi there, I would like to create a formula in column I to calculate which branch can transfer out stocks to those fast moving branches within same country. Refer to below table: Branch code 2002 (in Malaysia), my stock on hand is 1 pc, sales per month is 6 pcs, I have to transfer in about 5...
  7. R

    Get all matches in excel

    Hi everyone i have one excel sheet as below and need our help. please refer below example: I have data in A1:B4. I want the same in as desire as D1:f2 I want to all the possible match in rows format. please help. A B C D E F 1 Australia One Australia Japan china 2 Japan One One...
  8. C

    Userform Data Mismatch issues

    I Have created a user form that auto populate fields 2-11 with data from the range on sheet1 ("Data12"). all of this is based on the Serial number inputted into form field number #1 which is a dropdown list that uses the range in colum 1 of the sheet which holds all the other data the only...
  9. M

    Sum Index Match with multiple matches

    Looking at the below example, I'd like to be able to get the total hours of the individuals in column H by pulling their hours from Column E. Notice if there is more than one instance of someone's name, it only sums the first result. How might I be able to get the sum of a person's hours...
  10. S

    Index and Match Multiple Critera

    Hi, I am trying to compare last months cost ledger with this months. Last month I went through some 7000 line items and assigned them a cost heading manually as the accounts system does not do it for me. I don't want to revisit another 7000 line items in the new ledger rather the new ledger...
  11. K

    Index/Match returning first non-blank cell

    So I understand index/match will return the first instance of the match it finds, even if the answer is "". What I need is a formula that will return the first instance where the cell is not "". Here's an example: Customer No. Salesperson Code C000176 C000176 DG C000176 DG C000176 DG...
  12. E

    Index / match with several criteri

    Hello Guys, I m trying to do Index / match with several criteria but the formula is not working. {=INDEX(PointageStructure!$G$2:$G$1000,MATCH(1,(D2=PointageStructure!$B$2:$B$1000)*("Sub-task"=PointageStructure!$C$2:$C$1000)*("Analyste"=PointageStructure!$M$2:$M$1000),0))} Do you have any clue...
  13. S

    Pulling list of Unique Names from Refresh-able Table between Date Range (Or Month Number)

    Hello! Thanks for any help in advance: I'm attempting to create a "dynamic invoice" of sorts, with names being pulled from a "refresh-able" table. By that term, I mean an excel table that (I'm assuming) is pulling from an SQL query and just needs the "refresh all" button on the Data tab to be...
  14. C

    Index Match with Multiple Criteria in VBA

    Sub F851_Service_IndexMatch() 'Range("I2:I1073").FormulaArray = "=INDEX('[05-06-20 Service Crosswalk.xlsx]Xwalk'!$F:$F,MATCH(1,(A2='[05-06-20 Service Crosswalk.xlsx]Xwalk'!$C:$C)*(B2='[05-06-20 Service Crosswalk.xlsx]Xwalk'!$B:$B)*(F2='[05-06-20 Service...
  15. D

    Summing values across multiple columns, searching for first column in range

    Can anyone help me with the following problem? I have a data structure like below, except that it's a lot wider and goes from the previous Monday to 365 days out across the width of it. There are about 1200 rows of data. 11/5/2020 11/5/2020 12/5/2020 13/5/2020 14/5/2020 15/5 100%...
  16. sycodiz

    Hlookup/Vlookup help-this should be easy but I just can't figure it out

    I have a very simple excel sheet. I am going to break out sections and give them to different team members. No team members will have the same data. I need to match on Vendor number (Rows) and dates (columns) as shown below. The main sheet will be pulling the data from each of the other sheets...
  17. K

    VBA compare two columns in separate workbooks and copy row information (named ranges created)

    Hello, I'm new to VBA and coding is NOT my primary job function--I'm just trying to make a tedious task far less tedious for everyone involved. I have two workbooks with varied number of rows--one has consistent columns, the other's change both in column order and content. I need to pull the...
  18. B

    Maybe an Index Match Solution??

    I have the following problem.... Cell P9 on Sheet 1 will have a rate that comes from a matrix on Sheet 2 Sheet 2 matrix has descriptions of the operations from cell L5:L50 Sheet 2 has my years across row 4 cells O to Y Sheet 2 has the rates inside the matrix - cells O5:Y50 What I need to...
  19. E

    Index Match Formula Shows Wrong Result

    I have two table, this one is the initial table that contains raw data (on Sheet 2) ABCDEF1NoUserDateTimeActivityDescription2A-1Staff 305/06/20209:00testtest3A-1Staff 305/06/20209:30testtest4A-1Staff 305/06/202010:00testtest5A-1Staff 305/06/202010:30testtest6A-3Staff...
  20. 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))

Some videos you may like

This Week's Hot Topics