Forum Discussion
Adding data ONLY when two cells match across sheets...
Hi all,
Apologies: this is complicated...
I'm working across x2 sheets in an Excel book.
On sheet 2, I have my export page, where I paste the uploaded data for it to fill in sheet 1.
On sheet 1, I have the historical data which I need to add to weekly.
Sheet 2: Export Page including current formula (based on a working formula on another sheet we use. I need both forename and surname to match on the next page, and automatically paste columns C and D if/when/where they do, then tell me where any names have been missed (new students, etc.) so I can add them to the doc.
So if A2 and B2 were on A6 and B6 on sheet 1, that is where the data (C2 and D2) would be pasted, to ensure that the student retains their own points balances.
Sheet 1: Overview Page - I intend on the above columns C and D then being put into columns J and K on this page. I'll then just copy and paste the data across into the correct week following analysis, ready for the formula to re-populate those cells with the coming week's data.
I switched forename and surname around for readability but can return if that makes it easier (for GDPR purposes, I have removed all names). The formula there had green "Yes"s through the page when I used the original data. When I have gone to update today, everything is a red "No", as you can see, so I clearly don't have the right formulas!!
Appreciate your help, as I've been working on this for a couple of weeks now and just can't figure it out!!
The formula on the other sheet that I was trying to work from was:
=IF(COUNTIF(Table1[@[Name and tutor]],A2)=1,"Y","N")
6 Replies
Apologies HansVogelaar if this has come through twice!
Thank you for the comment, it has really helped as I now see that column C was supposed to be matched to the E column (so E2 in the example above), which was why I was getting 'No' responses!!
Do you have any ideas on how to do the next part - pull the data across from Sheet 2 Export through to Sheet 1 Overview, please?
I am looking for the 'pos' and 'neg' points to pull through and correspond to the specified individual by their names - Sheet 2 columns C and D through to Sheet 1 columns J and K.
The formula on the sheet I am amending to work for this different purposes is:
- =IF(XLOOKUP(A6,'BI export'!A:A,'BI export'!B:B,"",0)=0,"",XLOOKUP(A6,'BI export'!A:A,'BI export'!B:B,"",0))
I can usually see the logic in formulas, but I can't wrap my head around how this is matching up names and year groups then dropping the data in the correct place (although I know it does work!!). Owing to this, I'm really struggling to amend it!!
In row 3:
=LET(result, XLOOKUP(A3&B3&C3, 'BO Export'$A$2:$A$1000&'BI Export'!$B$2:$B$1000&'BI Export'!$E$2:$E$1000, 'BI Export'!$C$2:$D$1000, 0), IF(result=0, "", result))
I appreciate this, but unfortunately it is just coming up with a red box and stating that there is an error? I have amended the sheet name to ensure it fully matches, but it's not correcting the error :(
Aha!! HansVogelaar - I hadn't noticed that!! So that's a semi-solution and I'm very grateful!!
Column C needed to match Cell E2 as we have students with identical names but in different year groups! I'm now getting the 'Yes' that I needed!!
Do you happen to have any idea how to complete the other part? I need Sheet 2 Export columns C and D to fill Sheet 1 Overview columns H and I?
The original formula that I need to amend-to-fit is:
=IF(XLOOKUP($A706,'BI export'!$A:$A,'BI export'!B:B,"",0)=0,"",XLOOKUP($A706,'BI export'!$A:$A,'BI export'!B:B,"",0))
I understand why you want to match Beh colum B with A2 and Beh column A with B2, but why do you also match on column C? Wouldn't it be better to use
=IF(COUNTIFS(Beh!B:B, A2, Beh!A:A, B2), "Yes", "No")
