index & match

Hi everyone, Could I get some help with my formulae. Below is my pivot table I created from a data source. I defined this as PivotTableRange under sheet called Data - Pivot using =OFFSET('Data - Pivot'!$A$1, 0, 0, COUNTA('Data - Pivot'!$A:$A), COUNTA('Data - Pivot'!$1:$1))...

I am working with an Excel spreadsheet that tracks client orders and payments. I have two sets of columns: one for client names and their ordered quantities, and another for client names and their paid quantities. I need assistance with a function that accurately updates the payment status based...

I'm wanting to return a value that corresponds to another value that is closest to a target value that I determine. For example, in cell H4, I input the number 6 which returns 113 in cell J4. This currently works as 6 is closest to 5 in column B (mins) and the value 113 in column C (var1) is...

Hi, i am working with two tables. Table "Sum" looks like this. Priority 07/08/2024 08/08/2024 09/08/2024 10/08/2024 High 5 10 8 12 Medium 10 20 15 27 Low 30 15 27 13 none 50 81 38 67 The Table "Raw" looks like this Priority 07/08/2024 08/08/2024 09/08/2024 10/08/2024...

Hello, I am a new member and a relatively new Excel user so I apologize if this question has already been asked and answered, I thank everybody who will take the time to read and respond and I welcome any advice or solution for this problem. Ok, so I have added pictures to my workbook using the...

I have the following formula and data (please see images below). How can I improve or modify the formula to avoid slowing down the excel workbook? Note that the sheet contains around 25 tabs an the formula is used multipe times within each tab with various criteria. I would like to avoid...

Hello, I have a certain challenge, how to use a macro to make a certain formula apply in certain fields (example: fields between C5:G40 => "=INDEX(log!$D:$D;MATCH(1;(log!$E:$E=List1!$B13)*(log!$F:$F=List1!H$3)*(log!$C:$C="H13"))"). In cells (C5:G40) I keep track of the input of certain...

Hello all! There has to be an easier way to accomplish what I'm after, would love any ideas. I am trying to write a formula that spits out a value based on what range the percentage falls in. I'm currently using nested IFs to determine if the percentage falls in between the ranges below and...

Is it possible to replace all formulas containing IFERROR, INDEX and MATCH formulas with XLOOKUP? For example: this "IFERROR(INDEX(TB!B3:L9999;MATCH(A112;TB!A3:A9999;0);10);0)" to this "XLOOKUP(A112; TB!A3:A9999; TB!K3:K9999; 0)" <- (I assume this is a correct 1:1 replacement of the above)...

The formula I used below allows me to find the first positive number in the row. I cant seem to get it to help me find the second positive number after. I would also need it to find the third, fourth, fifth, and so on. =INDEX($J$8:$S$8,MATCH(1,INDEX(--($J$8:$S$8>0),1,0),0))

Hello everyone, I have an issue with an INDEX/MATCH function, and I would very much appreciate your help! For context: In the "CO" tab, I have one table with data for 11 different neighborhoods (names are in row 6) for all years from 2001 to 2033 (years are in column C). Starting from 2027...

Hi everyone, I'm trying to automate some reconciliations at my accounting job and running into some trouble. I saw some great threads here, but could not find my use case, so I wanted to ask. For some reason, I feel like it is a stupid error, as I usually can navigate INDEX/MATCH relatively...

Hello! I have a workbook that lists products we sell, for who, when and how many we have sold of each product. I would like to add macro into workbook, which based on multiple criterias can figurate the exact matching and return the requested relevant information, what I need. To simplify my...

Hi all, I am trying to do an index match with multiple criteria, but not to return an exact value and I'm having trouble doing it. Can anybody help? The idea is the following: I need to find the payment value (column B) for occurence #55 in IL If there was no "state" column, I would use the...

I need a formula that will sum together the values of the 4101, 4119, and 4122 that are labeled March in row 9. I can get it to return the first value the index match throws back, but I am having trouble adding the sum formula into it. Help would be much appreciated.

Hi I have reached the edge of my knowledge and need some assistance. I have a sheet that tracks crops and input chemicals, I need to summaries this into a table so that I can budget the cost based on the month the item is consumed. In Sheet 1. Data The first few columns are for the row...

Hello Struggling to get a Vlookup or an Index with Match to lookup a table of data in one workbook and get any corrresponding data from the other. I have a list of products in my first workbook which has monthly units sold in it. The second workbook has a manually copied Pivot Table of...

Hi, we have the data in sheet1 and sheet2 and using index and match formula, its working fine but we don't want to drag and drop formula from C2:C4 and again we have used formula in D2:D4, there is any formula from which we can get desired result so that don't do drop down SHEET1 125024568765...

Hi all I have the following sheet and I'm trying to use an index and match fomrula but it ignores a dynamic value. In colum O13, I'm using the index and match to return the person with the lowest value. This works as intended but I need a way to continue this but to ignore the name/person from...

I have one excel file that has two tabs that i want to work together. In Tab1 i want the priority list to be updated manually. Then on tab2 i want the priority list to be filled automatically from tab1. The order numbers will be in different sequence on both tabs so i want the priority to...