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

Occasional Contributor

# 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 two sheets together and find out from the individuals with the "most views" who from that list has the "most engagements."

Could folks recommend the best way.

I assume what way could be combine data using the pivot table with the emails as the common denominator and match engagements to the "views" list by email.

Then sort first by "views" then "engagements".

If that's the best way, could someone share the easy-to-follow instructions for pivot tables?

Thanks

15 Replies

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

You could do an xlookup here, using the employee email, on the Most Views sheet, look up the number of engagements for that same email on your engagements sheet. This will list out engagements along side views so you can have views and engagements side by side. Then use a "Custom sort" with two levels, to sort first by views then engagements.

Let me know if you need a more in depth explanation!

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

@DKoontz Thanks for pointing me in the right direction.

As I'm a newbie with , if you could provide me with a more in-depth explanation, I'd be most grateful.

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

I'll use two lists side by side so it'll be easier to show, but this will work across sheets too. So I would combine your two data lists using a look up, starting with this (your two sheets):

xlookup in column C  =+XLOOKUP(A33,E:E,F:F,"")  this transfers engagements from sheet 2, no matter what the order, and matches to the correct person or email in your case.

Select your data on sheet one and custom Sort:

Then add two sort by levels, first by views largest to smallest, then engagements largest to smallest:

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

I'm able to partially set up the LOOKUP, but I can get the formula to go through. I set up the sheet like this, but keep getting a "#NAME?"

Then I've tried multiple formulaS in C3 for the column C: "=Xlookup(A3,E3:E35,F3:F35)" ; =+LOOKUP(A3,E:E,F:F) . Nothing is working. Can you tell where I'm missing something?

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

As a comment, discussion could much more efficient if share sample file, not screenshots. "Nothing is working" is too abstract. Screenshot says only that XLOOKUP() is available in your environment. But returns result which you don't expect, and from screenshot is not clear which one. #NAME! error could be caused by few reasons, without the file it's long discussion to understand by which one exactly.

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

I know XLOOKUP works with the web version of Excel, as for desktop versions, I think it is available from Office 2019 onwards.

The alternative is VLOOKUP, Syntax: VLOOKUP(<value to lookup>, <range of data with lookup field on the left of the range>, <column number to of the range to return>, FALSE).  The lookup value must be in the first column of the range.

 A B D E F 1 SN Name SN Leads Saved SN Name Result 2 Yago 641 Mal 314 3 Michael 327 4 Mal 314

In the example above, the cell in F2 has =VLOOKUP(E2,A2:B4,2,FALSE).  A2:B4 is the range to look up the value with 2 being the 2nd column of the range.  The False on the end dictates if you want an exact value or the closest match (False = Exact Match, True = Closest Match)

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

Nope, it's not on 2019. Supported version are mentioned here XLOOKUP function - Office Support (microsoft.com)

IMHO, much better alternative for XLOOKUP is INDEX/MATCH.

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

Thanks, I can confirm it's only available on web-based platforms (including iPad, Android). Been using tablets for too long.

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

Index/Match is a bit less intuitive, especially if you aren't familiar with how lookups work.

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

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

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

Thanks @Sergei Baklan for the prompt. Here's the data. I appreciate you and @DKoontz looking at this.

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

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.

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

@DKoontz It still does not seem to be working. I'm using Excel 2016, so could that be at play?

# Re: Comparing Data from Two Excels - Most Views One & Most Engagements

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")``````