# index & match

1. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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...

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. ### 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);"")...

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

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

### Which adblocker are you using?

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

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