Forum Discussion
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.
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)
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.
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.
- 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
- 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.
Thanks SergeiBaklan for the prompt. Here's the data. I appreciate you and DKoontz looking at this.
True. I only would like to say that XLOOKUP was designed to take power of INDEX/MATCH in more intuitive form.
- Index/Match is a bit less intuitive, especially if you aren't familiar with how lookups work.
- Thanks, I can confirm it's only available on web-based platforms (including iPad, Android). Been using tablets for too long.
