1. M

    Select a random name with a changing array

    Hi all, I have to do 150 random draws to choose between 4 names, let's say A B C and D. But depending on the draw, sometimes there is only A and D participating, or A, C and D, etc. So the array is changing. I know this formula could do it if the array was always the same...
  2. P

    How to lookup a value from several rows in a table using INDEX and MATCH

    Hi everyone, First post, so be gentle! I have spent hours trying to work out a solution to this issue, and know someone here will know the answer. We have data in a large table with a column for each month and then new rows for each year (see screenshot). I need to lookup the value...
  3. 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...
  4. T

    Lookup Table in one sheet and Data in another

    Hi All, I have a data in one sheet in the workbook that in column A, it keeps track of individuals names, in B, the dates that they came into work, and in C, which shift they worked (work is divided in 3 shifts M, A, or E). I want to create another sheet, where one person's name can be typed...
  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. C

    Vlookup, Index & Match - Grab values and compare

    Greetings to all. First of all, I am new to this forum and need the help of your excel knowledge. I have a workbook with the Sheets: A, B, C. On the B sheet I am collecting data from sheet A, and trying to collect other data from sheet C. Since the VLOOKUP wont collect negative col_index_num...
  7. 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
  8. R

    The same INDEX/MATCH formual is giving different results, and I can't work out why

    I am trying to develop a new Excel file by simplifying an old one, because too many add-ons on the old one have made it too difficult to make constructive improvements. I have been pulling across worksheets individually, and the tried to make everything consistent. I have come across an issue...
  9. L

    Using INDEX MATCH to sum over a range, now wanting to add in an 'if' in to the formula

    Hi, Table 1 has many rows with costs in & in Table 2 I want to sum up these costs. Originally Table 1 did not have currencies in, so I was able to use the below INDEX MATCH formula to do this: =SUM(INDEX(2:6,N(IF(1,{1,2,3,2,5})),MATCH(C9,1:1,0))). Now I want to split the total of these 5...
  10. 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...
  11. 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...
  12. T

    Conditional Format Index Result Location

    I can't get the conditional formatting to work even though the index formula works in a cell. Here is my formula: =CELL("address",INDEX(Months,MATCH(B3,$B$3:$B$53,0),MATCH(VLOOKUP(B3,data!$C$2:$D$52,2,0),$C$1:$AS$1,0))) I copied this formula into the CF from cell C3 and applied it to my range...
  13. S

    Get updated table from other workbook with formula(there are duplicates, so no data model/power query)

    I am trying to get information from a table in workbook 1, and have it dynamically update in another table on workbook 2. I've tried everything, hours and hours No luck. Vlookup, index match, simple index. They keep returning huge errors. Please help me wonderful community of Mr. Excel! I...
  14. darrenmccoy

    Index Columns with formula - Help

    Hi Experts, Can you please show me a formula to index all the ID's into one column? The columns are all different number of rows. Thanks ContactID ContactID ContactID ContactID ContactID 10129844 10129173 9045269 9074122 9074288 10371234 11718250 9074122 9292922 10062097 10527196...
  15. M

    Count if multiple criteria with corresponding rows

    Hi All, Please see below. I need a formula (in yellow cells) that will return how many times the number is counted as well as the corresponding row returning a "yes". So for example, cell BA3 should be 6. Bit stuck with this one so any help will be appreciated. Thanks in advance, Mark
  16. J

    Return a row (containing dates), above a row of values

    Hello, please please can someone help me: I have a simple forecasting sheet, where each month a payment is made, until the value reaches £0. The example below, shows two lots of £500 per month - however if the payments were to increase or decrease this would change the month were £0 is...
  17. C

    IFERROR INDEX MATCH not pulling data when true

    Hello! I am having an issue with cells F5 thru F8. Cells F4 & F9 (directly before & after) are pulling the data, just fine... but F5 thru F8 are not. F5 thru F8 should be pulling the number 30. All cells are "general". Please see attached image/snip-it! Formula for F4...
  18. I

    FormulaR1C1 + Error 1004

    Hi, I have written some code that looks for column headings to locate the columns of data required in my formula, thus returning a column number. So logically the best method of creating my formula in the cell is to use the FormulaR1C1 function in vba, however I have been unsuccessful in getting...
  19. L

    Problem with INDEX, MATCH and IF function

    Hi guys, i'm losing my mind here trying to make work this formula, i have this text: Delivery operation - code 33X Box to open: 15 Box 15 opened succesfully User selected "error" on screen Action cancelled Delivery operation - code 44X Box to open: 18 Box 18 opened succesfully...
  20. R

    Index Match with duplicates

    Hi, I have a pivot table which lists football teams down column A (home team) and across row 2 (away team). The values in the pivot table are the game week that the fixtures will be played. From this, I've been able to create an Index Match which summaries the fixtures almost as required. The...

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