index & match

  1. R

    Using Index and Match for 1 column and two rows

    Hi, I have a spreadhseet with far too much information on it. I want to add a tab that will pull just the information I want from it. I've been trying to do this using INDEX and MATCH. But I think where the issues is, I need it to use information from two different rows. One row is the month...
  2. W

    Need help to find correct formula! Multiple criteria and range levels

    Trying to come up with a formula for a Sandbox that calculates an employee bonus's Criteria 1 - Type of Mgr - These 2 levels receive bonus based on % variance to budget * General Mgr * Asst Mgr Criteria 2 - Type of Mgr - These 2 levels receive bonus based on Store Level Sales (4 levels of Low...
  3. J

    Index Match Return Values

    Hello All, Thanks for all the support in the past. I would like to use the following formula =INDEX(Sheet2!C:C,MATCH(1,(N2=Sheet2!B:B)*(A2=Sheet2!A:A),0) )but it seems to not function properly. I have two worksheets open 1 and 2 = I have two criteria to match on. Column A and Column N on...
  4. I

    Partial match index match with array values shorter than match value

    Problem: Trying to run an index match_partial match on a long column of values to return the desired result but have been getting an #NA. Current formula without changes: =INDEX(level,MATCH("*"&J3,title,0)) So I'm trying to match these values to return "Middle Management" for values that...
  5. S

    Efficient way to use index match or alternative method to insert values dynamically into worksheet

    Hi everyone. I am stuck. I need to fill values into columns using VBA in much the same way as INDEX and MATCH does, but using VBA. I have everything turned off already. My data uses VBA to copy select columns from one sheet to a new sheet "Analyse" in Columns A to G. That works perfectly. The...
  6. L

    Stuck in a circular formula

    Hello! I am stuck in a circular formula, I understand why, I can't figure out the solution. Basically it's a shop list which is sourcing the area manager, assistant area manager, regional manager and region code information from a reference table which can be easily updated when details...
  7. M

    Index Match Issue

    Hey all, please help me solve this issue. I'm using a really simple index match to look up dates when a certain value occurred. The dates are located in the rightmost column ($A$5:$A$54) . This part of my formula doesn't change at all and this is what my initial formula looks like...
  8. M

    Index Match Issue with horizontal record

    I am sorry to bother if its a simple question but i got a trouble doing this stuff Here i got the Data like this A B C D Pk USA UK Aus 1 2 3 4 Ind Tur Can Nep 5 6 7 8 I Want results (Name of Country) in E2 if i put the number (given digits) in E1 E.g. if i put number 7 in E1, The...
  9. M

    2 way lookup

    Hi, I am trying to look for products from column B in column F. Once found I want to compare the 'capacity' with 'quantity'. If quantity is less than capacity for that product I want to display location, product code and quantity for that item. Can this be done with INDEX and MATCH or will I...
  10. B

    Complicated lookup reference in table

    Hello! Need some help with a dynamic lookup reference, likely using some sort of Index/Match combination as I'm trying to lookup a player name in a table based on his position and team name. For example, if I use the table below, the row reference in the first Match can only be B2:B16 or D2:D16...
  11. P

    Pull the subsequent data for repeat values (Vlookup/Index - Macth)

    Hi, I have a large data set with more than 15k rows. in one file (Say: File A) there is an SKU and Bin Location for all rows. And in the new file that I'm now working (Say: File B) contains the SKU number and I require to pull the corresponding Bin location. The challenge is that in File A both...
  12. J

    Index & Match Help

    Hello, I'm slightly above a novice with Excel. I'm trying to figure out a quicker way of returning values from a master data sheet to another sheet that is showing the individual categories. Master data has approximately 14 teams. Each team is scored on 8 different metric ratings every month of...
  13. R

    Return value based on multiple conditions

    Hi everyone I'm trying to return a value based on multiple conditions. The first condition is that the date is either equal to or between to dates. The second condition is that a specific cell in the same row that fulfills condition 1, has a value larger than 0. Please see the attached...
  14. A

    Index match VBA

    Hi i'm a beginner can you please help me to wright VBA script for "Index match" that returns value from other worksheet? please refer images i have 2 worksheets a and B in sheet b i need code that fills cells according to the column heading
  15. S


    I made a vlookup formula to indirectly get value from another sheet. The problem is the value i need is below two rows. I know i can't use offset in vlookup and i am not familiar with index/match. Can anyone help. The formula is =VLOOKUP($C$1,INDIRECT("'"&$B3&"'!A:AZ"),2,0) . Remember i need the...
  16. O

    Matching products with the same ID code

    Hi. I'm struggling with finding how to solve a particular Excel Problem. I have a table that comprises of labelled columns with lists below. The columns are as follows with an example of a single transaction: Unique Transaction Identifier Business Area Category Sub Category Product Code...
  17. A


    Sub Match() Dim Rng As Range Dim Cll As Range Set Rng = Range("d1", Range("d1").End(xlDown)) For Each Cll In Rng If Cll.Value < "" Then Exit For If Cll.Value > "" Then Cll.Value = _ "=INDEX(range1,MATCH(RC[-1],range2,0),2)" End If Next Cll End Sub range1 and range2 is Name Manger Column D or...
  18. M

    Searching an array of cells for a value and returning the column headers where a value exists

    Hi, I'm trying to find a way which i can return the column headers where a value exists in the cells F:M for the same row. As you can see from the screenshot for the first row, i would like to search for any value in row 3 and bring back the column heads for the rows. I have shown in Column N...
  19. S

    Ticket Prices: Date Ranges

    I am trying to calculate how much someone needs to pay me, where the ticket prices are set for a certain period. I have already have help to get this far, but I'm struggling to work out why the formula would work in Office365 and not Office 2013 In Cell F2...
  20. L

    Drop-down list with index(match

    Hey guys, has the website had an upgrade? Looks great! I'm trying to make a waste cost dashboard. Basically, all I want to do is select the supplier code from the drop-down list and once I enter the number of workshops run, it will automatically update the number of seats the event had space...

Some videos you may like

This Week's Hot Topics