Forum Discussion

Paul_PP's avatar
Paul_PP
Copper Contributor
Jul 06, 2021

Comparing Data from Two Excels - Most Views One & Most Engagements

I have two excel sheets.   One, I've sorted by "most engagements" and the other sorted by "most views."   Each sheet has the same emails for each employee.   I'd like to compare data from the t...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 12, 2021

DKoontz 

True. I only would like to say that XLOOKUP was designed to take power of INDEX/MATCH in more intuitive form.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 18, 2021

    Paul_PP 

    Excel 2016 doesn't support XLOOKUP(). Formula like this could work

    =IFNA(
  INDEX($F3:INDEX($F$3:$F$10000,COUNTA($E$3:$E$10000)),
        MATCH($A3, $E3:INDEX($E$3:$E$10000,COUNTA($E$3:$E$10000)), 0)),
 "no match")

    Please check attached.

  • DKoontz's avatar
    DKoontz
    Iron Contributor
    Jul 14, 2021
    To add to this, =+XLOOKUP(A3,E:E,F:F,0), the extra 0, will return 0 if not found instead of #N/A. Which may be helpful
  • DKoontz's avatar
    DKoontz
    Iron Contributor
    Jul 14, 2021
    No problem! I see where the mistake was, in your formula you have =+Xlookup(A3,E:E:F:F), it should be =+Xlookup(A3,E:E,F:F), you had an extra : where it should have been a comma. If you fill down this formula, it'll bring over all of the shares for each person. Where it returns an #N/A, that person isn't represented in column E, the share data set.

Resources