Forum Discussion

LarissaM711's avatar
LarissaM711
Copper Contributor
Nov 15, 2022
Solved

Need help for a reference Rule over a spreadsheet

Hello, I have a spreadsheet with date, campaign name and additional data from a source that are automatically pulled. Now I want to combine these numbers with data from a other source from the ot...
BI & Data Analysis
excel
Excel for web
Excel on Mac
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Nov 15, 2022

    LarissaM711 

    =IFERROR(INDEX('Sheet 2'!$C$2:$C$16431,MATCH(1,(B2='Sheet 2'!$B$2:$B$16431)*(A2='Sheet 2'!$A$2:$A$16431),0)),"")

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. IFERROR returns an empty cell instead of an error message if there isn't a match.

     

LarissaM711's avatar
LarissaM711
Copper Contributor
Nov 16, 2022

OliverScheurich 

 

Yeeees thank you that works!!! 

 

The rows will expand automatically, is there also a way to apply the formal to the whole "Pageview"-Column so that the formula does not have to be copied manually to each row???

 

Also can I just delete the exact row-numbers? Should work right? like this:

=IFERROR(INDEX('Sheet 2'!$C$2:$C;MATCH(1;(B2='Sheet 2'!$B$2:$B)*(A2='Sheet 2'!$A$2:$A);0));"")

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 16, 2022

LarissaM711 

=IFERROR(INDEX('Sheet 2'!$C:$C,MATCH(1,(B2='Sheet 2'!$B:$B)*(A2='Sheet 2'!$A:$A),0)),"")

You can refer to the whole column. However that would reference over 1 million rows and might result in an excessively long calculation time. Therefore i'd suggest to refer to e.g. 30000 rows. The formula can be copied down the "Pageview" column with the fill handle in almost no time. I can't tell if there is an easier way for this.