index

  1. B

    Help with Index & Match

    Hi I have reposted this as my previous entry was confusing and un clear. I am trying to Match data and pull data from one sheet to another. Worksheet "Attendance_Input" calculates percentage attendance across the row. The example below shows the December attendance for Liam Smith as a...
  2. Sumeluar

    Match a task if it is within a date range

    Greetings! I have the following formula: =IFERROR(INDEX('PM Schedule'!$C$3:$F$2500,SMALL(IF(('PM Schedule'!$E$3:$E$2500="Monthly"),MATCH(ROW('PM Schedule'!$C$3:$F$2500),ROW('PM Schedule'!$C$3:$F$2500)),""),ROW($B1)),COLUMN($B1)),"-") This formula populates from a large list all items all the...
  3. E

    Populating Dynamic List of Machinery Checks from Column Headers...

    Hi All - As an extension of a previous post... I've tried a few methods on this one and can get it working in principal, but hit snags with the row and column referencing dynamically... I would like a list of machinery safety checks, to populate dynamically based on them having an 'X' against...
  4. G

    Rotate an array index?

    Suppose I have an array X = {"A";"B";"C"} I want to rotate the index by an integer i, suppose i = 1, so that Y = Rotate(X,i) = {"C";"A";"B"} Is there excel function to do this? What I do now is Y = LET(X,H18#,i,H16,n,COUNTA(X),ix,SEQUENCE( n)-H16,INDEX(X,IF(ix<1,ix+n,ix))) which is quite...
  5. R

    Show most recent value within column (furthest row down)

    Hi, I have built a stock list for which part numbers are in Column B (ITEM CODE) and purchase price is in Column E (PURCHASE PRICE). Values increase over time therefore I require when an item is booked out to search for the most recent (lowest) value within Column E (PURCHASE PRICE), and add...
  6. L

    Cell reference when inserting rows?

    Hi, Greetings for all! I have a formula at Sheet2 as ...something(from this beautiful forum)..Sheet1!$A$2... When I insert 10 rows in Sheet1, formula in Sheet2 changes into ....Sheet1!$A$12.. How to prevent/fix formula to $A$2, how to fixate it to A2 Thank You in advance!! New Micro Excel...
  7. S

    Sorting through Data with Multiple Criteria (Index, Match, CountIF)?

    Hello, I am trying to itemize expenses by division of operations for my farm. I have a list of every expense made throughout the year. I need it summarized but flexible enough to account for differently named items that may be purchased in the future. Per my image uploaded: I am looking to...
  8. S

    Horizontal Return

    Hi I need to horizontally look at a data set & return the date of the most recent sale. in this example, 2 sales occurred in April 2024, so cell A2 returns 4/30/2024. A3 returns 1/31/2024 b/c 3 sales most recently occurred in Jan 2024. A1 return Formula 06/30/2024 05/31/2024 04/30/2024...
  9. SanjayGMusafir

    Index function stopped working

    Hi Experts Everything was going fine. Just now the following formula stopped working. I checked it, part by part. The INDEX function is giving error. In fact I realized the problem when found one of the Macro using same formula was not working as intended. Any suggestions Thanks in advance...
  10. M

    Finding cell with highest/lowest index from dropdown list?

    Wasn't too sure how to word this, but here is the situation: - A table ("Table1") with several columns; focus is on columns 1,2,4 - Column 4 contains a dropdown menu, which uses the list called "Steps", which is defined as G2:G6 - I need to find a given person's most/least progressed step (the...
  11. K

    Return Column header for MAX/MIN Value

    I have a spreadsheet that has the counts the number of instances on a daily basis. Under the table I have a breakout that finds the MAX and MIN number of instances. I would like to add a second cell next to that that returns the column header (Date) for the max or min value that is found. Im...
  12. H

    Lookup (or Search) for a value in one column across a range of columns, then return the corresponding value for a different column

    I have a data set that includes alternate SKU's for products. The alternate SKU's may be in any one of 31 different columns. I have a list of alternate SKU's that I want to look for i the range. When found, I want the formula to return the corresponding value in a different column. This will be...
  13. D

    Lookup value based on two column criteria

    i have been trying to solve the following issue: i have a very long column of values (in this case there are only two values = FY23, FY24, with FY23 always coming before FY24) and another column values (all zeroes except one value >0 in an FY23 row and one value >0 in an FY24 row) i want to...
  14. N

    MATCH and INDEX help

    Can anyone assist with unpuzzling what I did wrong with this formula? I am still learning how to create MATCH and INDEX formulas. Because of the vast amount of information in the file I would prefer not sharing the cleaned file because of how long it would take to scrub it clean. If I must, I...
  15. D

    Find a cell in a table, and return the value above

    So I'm trying to return a cell value from a table, relating to a text based cell as the searching tool. It's actually part of a larger formula, but the rest is straight forward. I am trying to return a result where if the day of the week changes in A2, it changes what cell I use in a formula in...
  16. J

    Most frequently occurring text in array formula

    Hi all, I am trying to create a formula which finds the most frequently occurring text value (all entries all text) in an areay I am using INDEX(D4:D7,MODE.SNGL(MATCH(D4:D7,D4:D7,0))) but this only works for single columns or single rows not a 3x3 or ZxZ array for example Can someone please...
  17. E

    How to have index match sum all matches

    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.
  18. S

    lookup return from range of dates

    I need to lookup & return an interest rate between date ranges. Any date between June 1, 2020 - March 31, 2022; return 2.00% Any date between April 1, 2022 - June 30, 2023; return 3.00% data table 2.00% 6/1/2020 3/31/2022 3.00% 4/1/2022 6/30/2023 3.92% 7/1/2023 12/31/2023 5.33%...
  19. A

    SUM function not functioning

    Hi, I am able to assemble a cell range (O21:O162) using this formula: =CONCATENATE("O21:O",ROW(XLOOKUP(INDEX(B21:B670,MATCH(TRUE,B21:B670<0,0)),B21:B670,A21:A670))) But when I insert it into a sum function, I get a #VALUE! error. Can someone show me what I'm doing wrong...
  20. P

    How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?

    I am trying to use MATCH and INDEX to improve performance on a google sheet that has way too many XLOOKUP functions. I have a helper column (DH) that lists the matching ROW I need from the Visibility sheet. This formula returns the expected result from column 9 on the Visibility sheet, where...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top