Forum Discussion
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 other sheet.
The difficult part for me is that the date and the campaign name from both spreadsheets have to match.
I attach a sample Excel. (However, for the original spreadsheet I work in Google Spreadsheets).
In the sheet 1, the yellow marked column should contain the data from "sheet 2" C but ONLY if date and name are the same, even if the row order in sheet 2 is different than in sheet 1.. it only needs to be assigned correctly
Please help me 🙂
=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.
3 Replies
=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.
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));"")
=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.
