index &match

  1. V

    Count basing on multiple matching criteria

    Hi, I had a excel workbook .with 2 sheets. Sheet1 consists of data as below: From A4 to A6 vehicle info(Lorry, Minivan & Truck), B1 to B6 Fruit(Apple, Mango & Lemon) Info and C1 to C6(1/11/2023,1/11/2023 & 1/10/2023) dates Sheet 2 is having the info from Sheet 1 like as below: From A4 to A6...
  2. J

    Prevent certain input from appearing in a range

    Hi everyone, Understand my problem can be complex. Allow me to explain in detail. If you refer to my excel file, there were a number of different types of (MI) Measuring Instrument types (TPG, PG, CMM SRT etc) Using my current existing array formula in cell F111:F145...
  3. H

    How to index match with multiple different columns

    I have been using an index match to pull data from a query to one of my spreadsheets. At first I was just having my query pull one day at a time so it was pretty simple. Now, I am having the query pull data from March till present and I need to index match my spreadsheet with multiple different...
  4. I

    Use two criteria on one table to find a matching salary on a second table.

    I have a workbook with two worksheets. on the first sheet, I want to use years of service and category to then derive the salary from the second worksheet. I believe that this will be a combined formula for lookup and index/match. Unfortunately, this is getting way beyond my abilities...
  5. S

    Multiple criteria (date range, product code, value) vlookup and if functions or do I need index and match?

    I apologise if this has been asked before but I can't seem to find an answer. I need the end result to be a discount given based on the product code, date range and quantity. For example: Based on the order date, product code, and quantity from the below table the discount should be -15.00...
  6. H

    How to split the name and display the status of task/project for each name?

    Hi. I want to create a summary report. The table below (at the left side) show that project A is in progress and managed by M1 and M2 (need to key in by staff). To create a summary report (table at the right side), I use INDEX and MATCH (with the help from helper column). But the problem is, it...
  7. T

    Return Date from Matched Adjacent Cell which is Closest to Current Adjacent Cell

    Hi all, I currently have two tables. Table 1 includes a patient's Unique ID and when they visited the hospital (Hospital Visit). In Table 2, there is also the Unique ID, but instead of Hospital Visit I have the date that the patient's primary doctor called to follow up with the patient after...
  8. M

    Returning value from a previous non-empty cell of the table's column to another table using first table's first column and header as criteria

    Dear friends, Good afternoon! I tried both English- and Russian-speaking Google, but couldn't find ANYTHING! So, basically I have two Excel sheets - on the 1st one, I have a table containing prices for the futures at a date (column A) for a particular futures code (row 1). Please, see the...
  9. F

    Strange Error

    Does anyone have any idea why this code only searches the first row with a matching header instead of every row with a matching header? =COUNTIF(INDEX($A$4:$F$360,MATCH(LEFT(L$4,3),LEFT($A$4:$A$360,3),0),0),$J16&", "&LEFT($K16,1))+N16 Array $A$4:$F$360 with header $A$4:$A$360,3 trying to...
  10. C

    Offset Index Match

    I need to get the column next to my target value here is my data sheet Sheet1 Oct Nov Dec Points available Points Achieved % Achieved Points available Points Achieved % Achieved Points available Points Achieved 0 0 #VALUE! #VALUE! 0 0 0 0 0 0...
  11. O

    Two Tabs - One Static Reference Data - One I update Manually - Want to pull Data from Tab 1 into Tab 2 in correct Row

    Hi guys, Please can you help? I know this will be very basic... I just don't know if it's a V-LOOKUP or Index & Match - neither I am good at! Tab 1 is data from data scraping. It pulls in from eBay in CSV (using Parse Hub) and leaves me with several unique rows of data across across a number...
  12. W

    Index Match 2-way conversion to VBA code

    Hello, I am trying convert index match formula to VBA code and fill 47 cells with it. Original formula =INDEX(Copy_Match!$B$4:$Y$34;MATCH(P!A557;Copy_Match!$A$4:$A$34;0);MATCH(P!C557;Copy_Match!$B$3:$Y$3;0)) Current code: Range(ActiveCell, ActiveCell.Offset(47, 0)).Value =...
  13. G

    INDEX and MATCH formula

    I need help creating a formula to look up values in order in the rank column in the table on the right, and pullover the Group A country onto the table to the left. The idea is that I hide the table to the right that is calculating scores based on another area in my spreadsheet, and I will use...
  14. D

    Sum of a text from table when met two criterias

    Hi All, I am being trying to get the some of "Blanks, Yes and No" from a table. But it has to match the "A" Column and the "1" Row. (More like Count ifs) <tbody> Name Cake Fruit Salad Cake Juice Fruit Salad Juice Apple Blank No No Yes Blank No Apple No No No Yes No No Apple Yes Blank...
  15. A

    Return corresponding names in descending order based on next column values.

    Hi everyone, Column A1:A5 contains unique names/references Column B1:B5 contains numbers I want column C1:C5 to be those names but based on their corresponding values in descending order. I don't want to do any filtering, a function on C1 to read values from B1:B5 and return corresponding...
  16. D

    Index Match formula and absolute column reference issues

    Hi, I have a workbook which contains 2 worksheets: 1 called MIPA and another called Dashboard Data. I am using Index Match formulas on several columns on the MIPA worksheet which refers to a named range on the Dashboard Data worksheet called Data (the data) and another named range called...
  17. H

    Extract Rows/Cells from Master Table to Sorted Tables based on Suffix of Text.

    Hello fellow excellors, I have a number of assets that contain very specific suffixes, for example: XRD05_CV XRD06_CV XRD07_CV XRD08_CV_I XRD08_HB XRD08_WS XRD09_CV These assets are listed in a master table. as you can see, there are different suffixes such as _CV, _CV_I, _HB and _WS. Now I...
  18. M


    So I have a workbook with two sheets Sheet 1 (Demand) Has a table of all of the items on the sales for a specific date and the quantities. Column A= Item # Column B=Sales order # Column C=Date of sales order Column D=Qty of Item on order <tbody> Items Sales # Date Qty Item 1 SO#1 9/1/18 5...

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