Share via

#REF index/match formula error

Anonymous
2024-06-25T15:48:16+00:00

I am trying to pull data from one sheet to another, where the data is printed vertically from the original sheet, and the data to be printed needs to be horizontal. I need three rows of data per project, so I am just highlighting the column of data I need for my formula to only use in the new sheet. For the first 3 rows of project1, it printed just fine, but when I dragged it down for all projects and each of their 3 rows, it gave me #REF for all cell blocks. here are my formulas for each measurement needed of each project. To include, from the original sheet the column for FE and ME are merged cells. Column C in sheet 2 are the names for projects. Thank you!

FE =(INDEX('Weather Performance Re'!$E$11:$F$13942, MATCH(Sheet2!E$8,Sheet2!$E$8:$FJ$8,0), MATCH(Sheet2!$C9,'Weather Performance Re'!$B$11:$B$13942,0)))

AFE =(INDEX('Weather Performance Re'!$M$11:$M$13942, MATCH(Sheet2!E$8,Sheet2!$E$8:$FJ$8,0), MATCH(Sheet2!$C10,'Weather Performance Re'!$B$11:$B$13942,0)))

ME =(INDEX('Weather Performance Re'!$S$11:$T$13942, MATCH(Sheet2!E$8,Sheet2!$E$8:$FJ$8,0), MATCH(Sheet2!$C11,'Weather Performance Re'!$B$11:$B$13942,0)))

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-06-26T09:45:11+00:00

    Hello SNARBB,

    Welcome to the Microsoft Community, it's a pleasure to help you.  

    Based on your description, I understand that you seem to be experiencing data display errors when migrating data from one table to another, and we understand your feeling.

    In order to help you better, I would like to understand a bit more with you:

    • You mentioned that there are merged cells in columns FE and ME in the original table. Are these merged cells affecting your data extraction? Have you tried unmerging these cells and dragging to get the rest of the data?
    • Have you tried manually setting up the table below and populating the formulas and do you still have the problem above?
    • Have you tried to create a new table and change the current data to the newly created table to troubleshoot? We recommend that you also share your files with us by sending a private message and we will help you to do further testing, you can send a private message and check it out below:

    I want to learn more details and I believe we can solve this problem together. Please feel free to let me know if you have any further updates, thanks for your support and understanding.  

    Best regards  Isabella-MSFT | Microsoft Community Support Specialist

    Was this answer helpful?

    0 comments