# index

1. ### Fancy sumproduct / index lookup

Hi, Apologies for not uploading a workbook - company restrictions prevent me from this However, I need to consolidate the employee data in table 2 at the very bottom and present it as per table 1 below this message. the main issue I'm encountering is that the pay category runs across the row...
2. ### Index and match function doesn´t work for all cells

Hello, I would like to compare value in D6 with values in first column of table F10:G25 and when found just write down the value on right side in second column. I tried to use =INDEX(F10:G25,MATCH(D6,F10:F25),2) (cell F7) and then also =INDEX(G10:G25,POZVYHLEDAT(D6,F10:F25,0),1) (which is G7)...
3. ### 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...
4. ### Index and If formula together with two criteria?

Hi, I would like to know how I can make this formula to use two criteria: =IFERROR(INDEX(Vikta;SMALL(IF(Vikta[Grupper]=1;ROW(Vikta)-4);ROW(2:2));1);"") I have tried this but it gives me a a "#SPILL" error...
5. ### Show cell based on current date

Hello, I come for help :) My situation is very specific and I just can't get match/index functions to work (probably doing something wrong). Data structure: Item stock= yesterday's stock - consumption + production + incoming + delivery - modification + "+/-" => Throughout the month (year)...
6. ### Collect data from multiple sheets using Index/Match

Hi! I´m kind of a basic excel user and struggeling with collecting data from different sheets. First of, the Excel is in Swedish, so heres a short dictionary: #SAKNAS! = NA PASSA = MATCH -------------------------------------------- What i want to do: In my HEAD-tab is where I want all the...
7. ### Multiple Cell Check without VBA

Hi, I have an hard time being able to figure out a solution to my issue and would like to see if anyone can help or has ideas of how this challenge can be resolved. I am trying to find a formula that is able to tell me if a cell has changed from a number to a text (horizontally) resulting in...
8. ### Need AGGREGATE to list when category is mixed up with others under one cell

Hello, I have a Data-Validation Drop Down on Cell A1 that contains Board Game Categories and whenever I click them, I need excel to pull all the Board Games associated with that category and list them down in COLUMN A. So far, my code is only pulling Board Games that has that specific category...
9. ### Index and Match between 2 sheets in VBA

Hello, hoping you can help. I attached a quick example of the structure of my sheet. I have an issues list that is reviewed weekly, tab names change each week. I want my macro to Index:Match to pull the comments from last week onto this week's list, associated to the right issue number. When I...
10. ### Change Index/Match after balance is used

I currently have a macro that goes through inserting formulas into cells and a for loop that changes the colors of negative values in the balance column. I would like to change the "shop floor" value with one from further down in a list when the "Balance" is less than 0. My thought for this...
11. ### Create Drop-down List from Multiple Columns

I have different accounts listed horizontally as in the picture. I'd like to create a data validation in a new cell that containing all the accounts I have i.e. "My credit card" and "Paypal". I've been working at this problem for hours. INDEX and MATCH don't work in this case. Why real-world...
12. ### 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...
13. ### Title a Sub Routine with a variable.

I have over a dozen checkboxes. They are titled checkbox1, checkbox2, checkbox3... etc. They each have a sub routine that tells them what to do when click. These subroutines are identical except for the number that indexes each checkbox. While this was easy to create using copy/paste. It is...
14. ### Index/Match Formula - Sum if falls between a date range

Hi all, Please can someone help? :-) I am trying to find a tidier version of the below formula - what I am trying to do is basically add up the values in row 2 for the week before. The displayed dates are Mon-Fri (not inc Sat-Sun). Note, the below formula would be in cell F3, so on the 2nd...
15. ### Look up a value for which 2 pre-conditions need to be met (ID and Year)

How can I look-up the share (Column G) for the needed ID and the corresponding year? See attachement.
16. ### HELP! Index match, VLOOKUP, Not sure what to do

hello! This is my first post so please bare with me. I am trying to make a configurator for work. Here is what I'm trying to do: B22 is a Drop down list made from Data validation. This list contains different types of equipment that is placed on different Chassis. I want to be able to click...
17. ### How do I use Index & Match to reference a separate sheet?

I have the following test code that is working where column H is equal to what's in column B versus column D Range("H2:H17") = "=INDEX(D2:D17,MATCH(B2:B17,B2:B17,0))" Column B Column D Column H A Test1 Test1 B Test2 Test2 C Test 3 Test3 D Test 4 Test4 E Test 5 Test5 F Test 6...
18. ### Complex INDEX MATCH - Now Add Max

Hi All I have a couple of Index Match formulas that are working well for me but I've realised there may be more than one result to find and I only want the latest date. My formulas sometimes have two match criteria and two index location, but I'm not sure how to return the highest valued...
19. ### Array not erasing

Hi, I cobbled together some code from multiple sources that will process text files. Since we have multiple files in a folder that need to be read, I built the process to read all of the filenames in a folder into an array (DirectoryListArray()) that I use as a master list of files, then...
20. ### INDEX and MATCH Function

I am trying to use the INDEX and MATCH function to look up a date within a range to bring back a annual salary amount. Here is the LOOKUP table which is on a sperate tab in the workbook call Pay_Scales: Annual Salary Start Date End Date 46395 2018-12-17 2019-05-02 47789...

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...