index and match

I have a spreadsheet that has over 100 companies and their associated plans, locations and rating tiers (1-4). The user would manually select the company, plan and location and the formula would retrieve the 4 different rates. I tried using INDEX MATCH, but the problem is if the rates are not in...

Hi! I'm currently working on a project where I have to match a reference number to one number from a separate column that is within +/-3 of the reference or equal to the reference. What formula can I use to do this?

Hi All, Apologies if the title doesn't explain my query best, but I have data in separate worksheets and want to create a search filter with set criteria. Cell A2 is a data validation list to select the desired test. Cell B2 is the select the mathematical operator for greater or less than. Cell...

Hi all so i am trying to do something that i don't know if is possible I have created a table for training courses that can work like a calendar The course details ar within the red boxes and can be anywhere within the range - but always come as a block. on my team view sheet - The formula...

Hi, I have a table that looks something like this: ID Level1 Level2 1 Size Tall 1 Weight Heavy 1 Colour Brown 2 Size short 2 Weight light 2 Colour blue 3 Size medium 3 Weight light 3 Colour white Where the same ID will appear in multiple rows but every time with a...

Hi, Looking for assistance with a formula. My best guess is it will use Index, Match & Min. I want to look in this range and find the Minimum value. =MIN(F3,H3,J3,L3,N3) I then want to return a column header for that value. Headers are in Row 2 This is also from a larger set of data if that...

I am trying to use an index match to have a dynamic sumif, formula below. For whatever reason I can only pick up data that is in rows 12,000 or above. My formula does not have any row range limits so i am wondering if there is a limit of variables for criteria 3? Or is it something to do with...

Hi Friends, I need your help to lookup and find out a specific value from a list. I am using office 365 and below provided a sample data of my requirement. CategoryStatusCategoryResult for NewDataOldDataYesDataNewStyleNoDataExistingStyleExistingStyleOldStyleRejected I need to check whether...

Hello, I'm looking for a formula to return the most commonly occurring text in a range minus a specific phrase. Is there anything I can add somewhere into the formula I already have, which returns the most commonly occurring text? Or do I need to rewrite what I already have? Here's what I have...

First off thank you for the help! I have a daily schedule on Sheet1 and have the Employees entire year schedule on Sheet2. So what I have been trying to do is get the names from sheet2 on to Sheet1 by date and position. I have tried INDEX MATCH looking for the date then the positions to no...

Hello all, New to the thread so I do apologies if this isn't sufficient. I am trying to create VBA code for Index Match Match with a loop. I have attached an image to help. To detail the problem; - Cells A4:C13 have permutations from myarray (1,2,3,4,7) where I want 3 numbers picked and...

Dear all, I have a data table of currency values, the top row contains all the currency pairings (GBP/EUR, EUR/USD etc). Whilst the columns on the LHS of the sheet contain information on the location and owner of the currency. I am trying to write a formula capable of pulling out all the...

Hello, I hope you're all well and keeping safe. I hope you can assist me with what could be an easy fix. I have two sheets, the first sheet is a summary sheet and the second is a raw data/ data dump sheet (please see example below): Sheet 1: Summary KPIs Week 19 Week 20 Revenue 100...

Hello forum, I have a workbook, that I use to import data into our ERP. I have 3 columns(E,F,G) that I will input a number into. I want to concatenate the 3 columns in Column H. While inputting nothing if the column is empty. EFGH1Depot 1Depot 2Depot...

Following is the screen shot. We are suppose to get following values - the first value from right side - year of first value - 2nd value - year of 2nd value In order to get the year of 1st value we employed INDEX MATCH LOOKUP...

Hello, I am currently using this formula to return data from one table that has a "Student ID" column. Sometimes the Student ID is repeated in the table and so I want to return all the rows that has the same Student ID in that table. With this formula, I am getting just the 1st row...

Hello all! I have spent many hours attempting to solve this problem and hope one of the geniuses on this forum could please help! I think I am doing something simple wrong, and it might just take a quick lookover. I have a monthly task where I need to take my employer's budget from their...

Hi everyone, First post, so be gentle! I have spent hours trying to work out a solution to this issue, and know someone here will know the answer. We have data in a large table with a column for each month and then new rows for each year (see screenshot). I need to lookup the value...

Hi, I have a fairly complicated Excel file using lots of Index and Match formula to grab data from another file and format it to how I need it. There are 63 cells per row, with 41 containing formulas (22 without formulas). I need to copy all formulas and data from all 63 cells, except column A...

I have a formula that uses a match and index function to display the correct data. I have a dropdown list of values in cell ("B3"). The formula finds the matching value of cell ("B3") on sheet ("Competitor Comparison Data") which allows me display different sets of data, depending on what value...