index & match

  1. A

    Give back the most/least profitable product

    Hey Guys/Girls, I need some help with getting back the most profitable product in a single cell and a least profitable in another. I had a working formula but it didn't worked with empty spaces. I have a summary sheet and a second data sheet where i store all the information. On the summary...
  2. L

    Sum over an array based on date in row and unique identifier in column

    Hi, I am looking for a formula to put in cells B2:F7 on Sheet 2. Notes - This is a simplified version of the file I am actually working with. The file I am working with has all work-day dates (Mon-Fri) in row 2 on Sheet 1, for 01/01/2021 to 30/06/2022. - Each Trade Reference is unique, so...
  3. K

    Look up Number in a list and return matching text

    IJKLMN2To be Looked intoOutputNameID3ABC(1234),FGH(8976),EFG(4567)ABCLooking or a Formula in J3ABC12344FGH(8976),EFG(45678),CDE(3456)CDELooking or a Formula in J4BCD23455CDE345667Need to look values in Col N in Col I and if matching found return the associated name8Ex: 1234 is in Cell I3 and...
  4. D

    Matching based on rows

    Hi everyone, I am looking to create a formula (I am assuming using Index & Match & IF??) that will provide me with a text output (for later use in Pivot Tables) to indicate this data should be removed. As you can see in the picture, I've already got a column (column S) that refers to many...
  5. Jyggalag

    Iferror(Index&Match&Match) formula

    Hi all! I currently have a large worksheet that is similar to this: With the master data looking like this: I want to create a sort of IFERROR(IF(INDEX=0, ,MATCH and MATCH formula (with an iferror that returns "" (blank) if there's an error)) for every column with [] such as [DATE]. So...
  6. R

    How to fetch the highest or successive highest values?

    Hello everyone, Kindly help with the formula that could search with the given query id (column F) for the highest %Gene identity (column D), which could fetch the values for %gene identity (column I), the gene (column H) and relevant accession (column G). However, Query ID should match the...
  7. V

    VBA index match array

    Hi, im trying to use an index match formula in VBA to skip the rows that have already been input in to the userform (NameBox1VF...) but so far the code im using will only populate the text boxes with the first rows data "E2" instead of continuing down the column. Private Sub...
  8. Jyggalag

    IndexMatch function not working, please help :)

    Hello everybody, I currently have something similar to this: Linking to this: It should return the date 21-06-2019 for XG10, but it does not. Can anybody see what I did wrong and how to fix it? Would be greatly appreciated! :) Thank you! Best regards, Jyggalag
  9. V

    Index match funcion ignoring blank cells

    Hi everyone! I am using these formulas to rank names and the problem I am having is that they are returning blank cells: =COUNTIF($BZ1:$BZ608;"<="&$BZ1:$BZ608) =IFERROR(INDEX($BY$1:$CA$608;MATCH(BX1;$CJ$1:$CJ$608;0);1);"") =IFERROR(INDEX($BZ$1:$BZ$608;MATCH(BX1;$CJ$1:$CJ$608;0);2);"")...
  10. T

    Cell address issue

    Hi, can someone please help me with the below formula, I am after just the cell reference but the result also brings through the name of the workbook and worksheet, any help would be great =CELL("address",INDEX(INDIRECT("'" &$A2&"'!f2:z10"),MATCH($C2,INDIRECT("'" & $A2 &...
  11. Jyggalag

    Excel not showing blank in formula when I want it to

    Hi all, I currently have this formula: Right now the cell is showing a blank cell. I then have another cell that links to this cell through this formula: This cell also shows blank, because the first cell is blank. I then have a third and final cell with this formula: However, this...
  12. Jyggalag

    Help with possible IFERROR(INDEX(MATCH))) formula

    Hello everybody, I currently have an excel sheet that looks similar to this (but much larger): And then a master data sheet next to it that looks like this: Please note that some of the titles are not in the master data set (in reality however, they all are, i just created this example...
  13. W

    Index/Match with Wildcard - VBA code

    Hello all I hope someone may be able to help me. I have a large data set that I utilize index/match for to pull out keywords based off of a defined data set (actually 3 separate data sets that change frequently). I am wondering if there is opportunity's for the following. Speed up my index and...
  14. M

    Countifs combined with multiple index/match

    I just can’t crack this one. Can anyone give a hint in the right direction, please? I have this one sheet (Daily presens) where all of the leaders for each departement enters if the staff is pressent. Then I have another sheet (qualifications) that contains all the certifications each worker...
  15. T

    Return the end time for a person ba looking at the start time for another task

    Hi, everyone. I have a table of people with a date, start time and end time, as well as task performed by that person. The problem is that most cells in the colum of end time are blank and I would like to take the start time of that person on the same day for another task (the person changed...
  16. E

    How to distinguish duplicates using a SORT function

    I am sorting data from a large table (called Games) for the top ten fastest running times, I will be continually adding new data to the table and the top ten times may change moving forward. I have attached a picture to be used as a visual, which also includes the function I am using. The...
  17. D

    Try to use Index and Match Function

    Trying to use index match function this code all looks right? But will not enter the value into the correct cell on the Destination sheet Private Sub Jobcard_Demands_Click() If Jobcard_Demands = ("Drawing No`s Update") Then Dim PartsList As Worksheet Dim wsDest As Worksheet Dim...
  18. M

    plot scatter line based on lookup value

    Hello all. First post. I have two design cases, LE and HE and their results as a function of depth. I have these at 95 locations. I would to plot them, with a lookup function for any one of the locations. So I enter the location into a cell, it finds the depth and LE / HE results for each...
  19. C

    INDEX MATCH with Multiple Matches

    Hello - I'm working with a large spreadsheet consisting of sales data. One column lists the opportunity IDs and another column lists the tags associated with the opportunity (among other fields that are not relevant for this particular question). The catch is that each line item/row has only...
  20. B

    Macro that concatenates two cells, references a Date and then copy pastes value into first available row

    Would love some help writing this macro i've uploaded the file on dropbox with more explanations within the file if it helps make it clearer. (in yellow highlights & red text) dropbox file I have two sheets. One titled 'Money', the second titled 'Data' In the 'Money' tab, there's a section...

Watch MrExcel Video

This Week's Hot Topics

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