Jul 06 2021 03:34 PM
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
Jul 07 2021 04:02 PM
Jul 08 2021 04:35 PM
@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.
Jul 09 2021 09:51 AM
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:
Jul 11 2021 07:42 PM
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?
Jul 11 2021 11:35 PM
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.
Jul 12 2021 03:12 AM - edited Jul 12 2021 03:14 AM
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.
Example based on your data:
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)
Jul 12 2021 03:27 AM
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.
Jul 12 2021 03:53 AM
Jul 12 2021 08:49 AM
Jul 12 2021 09:43 AM
True. I only would like to say that XLOOKUP was designed to take power of INDEX/MATCH in more intuitive form.
Jul 14 2021 11:03 AM
Thanks @Sergei Baklan for the prompt. Here's the data. I appreciate you and @DKoontz looking at this.
Jul 14 2021 12:41 PM
Jul 14 2021 12:42 PM
Jul 16 2021 05:31 PM
@DKoontz It still does not seem to be working. I'm using Excel 2016, so could that be at play?
Jul 18 2021 10:46 AM
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.