index/match

Hi there, I'm having an issue related to how long a macro takes to complete. Originally my code used a significant number of WorksheetFunction.Vlookup formulas to pull the data I required into my Allocation Sheet. After doing some reading/testing it seems that my code below is now a slight...

Hi, I have an Excel worksheet that has to do an index/match to look up data from another sheet. It has 14 rows and ~8,000 columns -- each with an index/match. So, as you can imagine, the file is huge (60 MB) and too slow to be usable. I'm thinking I could instead have a macro do the lookups...

Hi, the following formula works great until there is noting found in the index/match =IFERROR(INDEX('Details'!$C$2:$C$10000,MATCH(E10,'Details'!$B$2:$B$10000,0),0),"") Is there a way to show"-" instead of the "0" returned Many thanks

Can someone help me combine an indirect with an index/match. Here is my index/match formula: =INDEX('2017'!H5:S16,MATCH(B9,'2017'!B5:B18,0),MATCH(C4,'2017'!H4:S4,0)) This works perfectly. Obviously it is referencing data in a 2017 tab. But if the date in c4 is a 2018 date, I need it to...

Hi there everyone, I really need an Excel formula or macro (ideally copy/paste since my VBA is not great) that lets you change the index and lookup array based on the question identifier. So essentially looking at data below, I want my index to change from C2-C4 to C7-C9 based on the identifier...

Hi, I have a large set of data (50k+ rows) which contains staffed hours at 30 minute intervals for 3 different locations. What I am wanting to achieve is a total number of staffed hours for each 30 minute interval. (combining the 3 sites) So far I have tried SUMIFS (=sumifs(Staffed Hours, Time...

Hi Everyone, Using Excel 2010: On Sheet 1 I have a product name (column C - populated) and product provider (column D - unpopulated). I need to get the corresponding product provider for the product name. The thing is that this information is somewhere in column C of either sheet 2 OR...

Hi All I am having trouble with the following situation I have a data set 1 A B C D 2 GDP 100 200 300 3 BBB 400 500 600 4 XYZ 700 800 900 I would like to do an index/match formula that returns the largest value for GDP which would be 300 by searching...

Hi, I need to search multiple sheets on excel and bring the data into a single sheet. I have 10+ sheets like the example below. <colgroup><col span="2"><col><col span="5"><col><col><col><col></colgroup><tbody> Tracy Reference No. Client Review Area % Done Loan Count...

Hello, I am working on a COUNTIFS statement and have 2 of my 3 criteria down. =COUNTIFS($B$16:$B$179,"<>*L2*",J$16:J$179,">0", M$16:M$179,"<>"&"") This last range of data is where I cannot figure out how to use the formula. Range M16:M179 is a column I created by using an INDEX/MATCH...

I'm doing a massive number of Vlookups on an old computer running the latest version of Excel . I've heard both that index/match is faster, and that it used to be, but isn't much anymore. Some vlookups are only in one column, some will be a few columns wide. I'm more comfortable with vlookup...

Hello! I am attempting to populate a series of cells (sheet 1, C4-I4) with store numbers based on users inputting a market name into sheet 1 B2 and using an index/match to return up to 7 unique matches in C4-I4. I have been able to write a formula to populate the cells with matches, but when I...

Let me preface with saying I'm no rookie to INDEX/MATCH and use this function often. I am trying to sum two values returned by the function - I am using the same arrays in BOTH FUNCTIONS and searching for the same value in both columns the second Index/Match function is returning a cell 4...

Hello, I have a spreadsheet which has 60,000 rows, calculations on every line over many columns. Using Index/Match a lot of the time, makes the spreadsheet very slow and large, are there any alternatives; or maybe someone could suggest a different approach? Many thanks.

Hello everyone, I was searching through the forum for a solution on using Index/Match but I didn't manage to find one solving my problem. I have two excel documents. The one of them contains information about participants of a quiz. Each week I have to retrieve the participants of the quiz and...

First let me acknowledge that each component of my question has been covered however I think this is unique, and probably simpler as I only want to return the value AND format of one cell, but it is from an closed external file. I'm new to VBA and the answers to each component of my question all...

Hello, I am trying to use INDEX/MATCH with LEFT, to change the returned value. Example: <tbody> Data1 Data2 5 Apples {are great}<are good=""></are> 10 Oranges {are also great}<are also="" good=""></are> </tbody> =INDEX(B:B,MATCH(5,A:A,0)) This Formula returns the value of Apples...

Hi, I am working on a VBA code for INDEX/MATCH function for a range of IDs. Sheet1 has table i.e. Table1 (which would be a dynamic range since there could be some additions or deletions over time) with name of emp in range (B4:B36) and emp ID in (C4:C36) (first row is header in the range...

Hello, I have a table similar to the one below. I created an index/match formula to find the row header for the highest value which works fine. However, in this case the max value repeats multiple times. For this table, I would need to create a second index/match formula that finds the next...

Hey Everyone, I have a formula that text joins three cells using index/match for each. The second cell being joined is a number. Is it possible to have the number being returned by the index/match multiplied by a number in another cell? If my current formula returned: Truck 5 Cat. The new...