index & match

Hello! I am trying to find a formula where, using a static book size (say $1550000) and dollars over goal (say 250000) it will return the % at which commission is applied & can then calculate the annual bonus. For the example this would be 4% as the book size is between $1.5m and $2m, and the...

Hello There, Reference to the attached sheet, what is the best formula that I can use in Dashboard tab in Cell AL30 to check in Extract tab if there are figures for that specific month and to return it for specific segment, for instance in AP30 in Dashboard tab, it returns the Occupancy for...

I have a data table say A1:N100 Column A is Category Column B is Sub Category Columns C:N would be Jan - Dec I need a dynamic sum that only sums up if Category A value is "Criteria1", Category B value is "Criteria2" and for only the month of my choosing. Sometimes this will be current month...

first, I'm sorry for another index-match question, I have noticed a lot of these topic here. But I can't find one that helps me. Possible that I just suck a searching. But I've learned a lot from this site. Working on the Dutch version of 365 Question one: My excel file consists of a...

Hi all, I am hoping one of you great people can help with a simple problem (I think) that I can’t get my head around. I am trying to use a COUNTIFS statement alongside a HLOOKUP. In Sheet 1 in columns C to F, I want to count the ‘Modules’ (Test1 to Test8) in Sheet 2 that = 1. The First...

I have two tables. In the first table are the rates by resource (columnA) and the MonthYear (ColumnB-Y) In another table, table 2, where I have the resource (columnA) date MMDDYYY (columnB). I need for table 2 to look up and match both the resource and date from table 1 and return the rate...

Hey everyone! Maybe its a bit of a hard to understand question but I will try my best. I am creating a custom ribbon and in this ribbon I want a "Easy index-match" button. When the user presses this button 3 pomps in a row will pop up. The first prompt will ask the user to select the target...

Hi, I am looking to create a calendar on the first tab of a workbook I have. Currently the workbook has multiple sheets of data, each sheet containing data related to a different type of work that is being scheduled in for different people. In order to currently find out what type of work...

I want to return a list of headers in column R wherever there is a value match in column N. Column n contains extracted phone numbers, which I use as ID's Where TRUE exists in any row of a column AB to AK, that header (AB1 .. AK1)) is returned plus a line break, for each of columns AB:AK...

Having trouble creating a formula that will populate "pay date" based on first criteria "color" and date that falls within date range. Is there a simple way to do this? For exampling hoping to achieve that the formula will start by identifying "BLUE" in Color column, Then return value from Pay...

Hello, I need to find the row that matches the most number of parameters. I have two datasheets. One table "Table23" with a set of rules that can contain a specific value or a *. Table23: In the second table I need to get values and match them against this table to find the truest match and...

Hello, looking to have a recap table of attendance as follows: - From the dropdown list you choose between 3 options and i'd like to have a count of sick days, timeoff and Tardiness for each employee for March, April... What formula would fit in ? I tried with some Countifs mixed with...

Hi all I have these two formulas which work but I need to combine into one formula. I tried a few things but unsuccessful and given up. I could create helper columns but rather use single formula. =IF($A11="","",IFERROR(IF(INDEX(PDA!$B$2:$B$1048576,MATCH(TRUE,ISNUMBER(SEARCH("*"&"Code...

Hello, I'm currently working on a project with employee data and need to create a table that displays each employee's score for 5 different KPIs during each month of their employment. I have a huge table with all the relevant data and was trying to create a pivot table or write a formula that...

Hi all, I am trying to use a partial text search in one of the matches and it is not returning any results. I've used this method numerous times in the past but never with a partial search. Can someone please take a look at my example below and let me know if they can help? In my example, I...

Excel formula for multiple matches. Kindly help to find a formula in G3 and G4. I have data in column B and C, I wanted to match it with column E and F. However vlookup does not work as there are multiple matches. I wanted a result of True/False if B and C contains the values in E and F...

Hi, I want to look up a number that is in column D from column E, and if it is found in a table on a different tab in column A rows 9-109 put the work 'Expenditure' in the lookup cell in column E, and if the number found from the lookup is in column A rows 121-160, put 'Income' in the lookup...

Based on this table, I need help on creating a formula in K3 to count and in K4 to sum based on the following condition In K3 --> countif B1:G1 = K3, A3:A5 = K1, B3:G5 is number...

Hi everyone, I've looked and found several posts about using the greater than match type in index(match formula's but none have any with 2 match references and I'm stuck. I am building an AQL inspection table and want the sample size to be automatically indicated for our team however the value...

I am having some difficulty finding where to start with this , i have found a few codes in other forums but am not able to manipulate them to my needs , so here goes my best to explain this - So to start i have a spreadsheet with multiple sheets , this is a POB (personnel on board) , within...