1. 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...
  2. 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...
  3. 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.
  4. 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%...
  5. 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...
  6. 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...
  7. R

    vlookup, INDEX, or SUMPRODUCT???

    This hopefully comes through okay. I am looking to automate the information going into columns G and H. I highlighted the date ranges to make this a bit clearer. In the real world I will have "Source 2" with samples every 10 minutes. "Source 1" is just whenever a fault occurs. The goal is...
  8. C

    Index/Match | Index/Small Formula

    Hello experts, I am looking for assistance with my formula. I am using an index/small formula in the following scenario: I am pasting raw data into Tab A, which includes status (example: Active, Inactive), and additional rows of data. In Tab B, is a summary table, that pulls rows of data from...
  9. D

    Search 2 columns, for MATCH in column in a second sheet, insert value

    Hi, I have 2 sheets, I want to search / match Sheet2 cols A or B with the value in Sheet3 col. C, if there's a match I also need both col A & B in Sheet2 to be numeric (neither cell blank), if these criteria are met then Sheet3 col D = "BZ" else blank. What's the best way to do this? Any help...
  10. B

    Find column by header AND filter by another column?

    I'm working on a table that references another and returns the number of employees in each of several regions, by year. The source table includes a row for each employee, with a value of '1' in the column for each year they worked: Destination Table: Year Region Employees 2020 a 1 2020 b...
  11. S

    Count across a data set or index

    Hi, I need a count formula that counts names across multiple columns. I need to know how many people attended Philadephia. The answer is 5. Then pull the formal down to know how many attended Orlando = 6. please note the blank cells are blank b/c there is a formula iferror return "" A1 first...
  12. skyh3ck

    1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help

    Hello guys i have a data base where i want to first find the column header, 2nd in same column find a certain date, and 3rd return correponding date from same row 1st column. So i have prepared one sample excel file Sheet one - Calc - where user can enter the class and dates Sheet two - Log...
  13. S

    Please help me with my workbook as Iam novice to Excel.

    Please help me with my workbook as Iam novice to Excel. I have 2 sheets, "STOCKS" and "ORDERLIST" .....I want to automate in Allocation Column the email address of the client matching the criteria of Product_Name,Size and Gender. its should return the email address as unique value, is drop down...
  14. k3yn0t3


    Hi there. Can anyone help me with an indirect with nested index match formula that's dynamic enough to allow me to get values from other sheets which change depending on the Factory and Metric I need them for? My hope is to have one formula that's dynamic to copy it across and down as the file...
  15. F

    How to show relative value in searched values?

    Hi all, I want to search within range A1:B16 the CODE for each NUM in D1 to D8 with one condition. There are some CODEs that are of the form XXXXZZ and XXXX24 that belongs to the same "group". The CODE that ends with 2 numbers is the "MAIN" CODE within than group. The "MAIN" CODES begins...
  16. B

    Index Match with zeros and duplicate data.

    Hey all, Thanks for taking the time to review my question. I must be missing something simple but for whatever reason I am missing something. In "Tab A" I have a list of "A" names, "B" dates and "C" total hours, In "Tab B" I have "A9:A39" each date of the selected month, "A1" the name I want to...
  17. T

    Combining Index, small, if, row

    Hi All I'm running into a bit of an issue I can't work out, granted I have a horrible cold right now... I have this formula to find content in a sheet called "Test", it works perfectly until later (G94)...
  18. D

    Which box for which parts?

    Hi everyone hopefully you can help me out with this as my head if fried. I have had a look at some of the other answers for similar questions but I can't see how they would work for my problem. I have a long list of parts in my inventory. I also have a list of boxes for storing the parts. I...
  19. Y

    Data Matching

    I havr data in Column AD that has the same date in column AC but no on the same rows. Column L is connected to column AC and AD but the corresponding rows has different names. I want to display the Name on Column L that is connected to column AD and display it on the row where Column AC has a...
  20. 9

    Index Match function to derive the entire list in a table based on a selected variable/s.

    Hi Everyone, I'm working on this unique case. I've a table (raw data) in which I have Column 4 columns named Country, Supplier, Parts, and Net Price. Country Supplier Parts Net Price Germany Intel Part 1 2.30 Germany Intel Part 2 2.34 Germany Spirax Part 1.A 2.38 Germany Romoss Part...

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
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 "".
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