XLOOKUP: 10 Advanced Tricks
Example 0
Empl ID AC0017
Name
Example 1
Get all columns
ID AC0021
Example 2
Get all columns, but show vertically
Example 3
Begins with
Name starts with harry
Full name
Date of Join
Example 4
Salary equal to or higher than…
Salary $52,000
Name
Example 5
Between two dates
Start 20-Mar-2022
End 1-Apr-2022
Name of the person?
ANSWERS
Example 0
Empl ID AC0017
Name Exact Hoover
Example 1
Get all columns
ID AC0021
#NAME? #NAME? #NAME? #NAME? #NAME?
Example 2
Get all columns, but show vertically
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Example 3
Begins with
Name starts with harry
Full name Harry S TRUEman
Date of Join 4-Aug-22
Challenge
One formula to get both results
Full name #NAME?
Date of Join #NAME?
Example 4
Salary equal to or higher than…
Salary $58,000
Name Richard Function
Example 5
Two conditions
Gender Female
Job Title Marketing Manager
Name of the person?
#NAME?
Example 6
Between two dates
Start 20-Mar-2022
End 31-Dec-2022
Name of the person?
#NAME?
Example 7
Highest Salary
Full Record
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Full Record
#NAME?
#NAME?
Example 8 #NAME?
Longest Serving Employee #NAME?
#NAME?
#NAME?
Full Record #NAME?
#NAME? #NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Example 9
2nd match
Job title Production Operator
Name #NAME?
Example 10
all matching values
Names #NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Name Emp ID
Barank Obama AC0001
Large Bush AC0002
Fill Clinton AC0003
Donald Roundup AC0004
Now Biden AC0005
ROWland Reagan AC0006
Jimmy Charter AC0007
Gerald Find AC0008
Richard Function AC0009
Abraham LinColumn AC0010
Harry S TRUEman AC0011
John IF Kennedy AC0012
Wood ROW Wilson AC0013
Franklin D Choosevelt AC0014
Ulysses S. GrandTotal AC0015
Dwight D. EisenLower AC0016
Exact Hoover AC0017
Amitabh FUNCTION AC0018
Shahrukh SCAN AC0019
Aishwarya ROW AC0020
Rajini COTH AC0021
Mammo T AC0022
Pra BASE AC0023
ROMAN Irani AC0024
SUM Antha AC0025
Alia BAHTTEXT AC0026
Kaj ALL AC0027
VALUE Arjun AC0028
ShaHIDE Kapoor AC0029
Paresh ROWLEVEL AC0030
Mani RatNAME AC0031
ViRIGHT Kohli AC0032
SachIF Tendulkar AC0033
Kapil DAY AC0034
VishwanaTAN AnAND AC0035
#NAME? #NAME? #NAME?
Gender Education Qualification Date of Join Job Title Salary
Male Bachelor's Degree 12-Jun-20 Chocolatier $51,300
Female Diploma 18-Feb-21 Production Operator $38,300
Male High School Diploma 5-Sep-22 Packaging Associate $31,400
Male Bachelor's Degree 20-Nov-19 Marketing Specialist $60,700
Female Master's Degree 8-Apr-18 Research Scientist $77,300
Female Bachelor's Degree 14-Mar-23 Sales Representative $49,800
Male High School Diploma 2-Jan-22 Production Operator $33,000
Female Diploma 19-Jul-20 Quality Control $41,900
Male Bachelor's Degree 10-Nov-21 Research Analyst $58,100
Male Master's Degree 25-Jun-19 Product Manager $82,900
Male High School Diploma 4-Aug-22 Packaging Associate $28,900
Male Diploma 17-Feb-20 Production Operator $37,300
Male Bachelor's Degree 30-Oct-21 Chocolatier $52,700
Female Master's Degree 12-Sep-18 Marketing Manager $74,700
Male Bachelor's Degree 5-Mar-19 Research Analyst $55,600
Male High School Diploma 21-May-22 Packaging Associate $32,900
Female Diploma 8-Aug-21 Quality Control $41,500
Male Bachelor's Degree 23-Nov-20 Sales Representative $49,400
Male Master's Degree 15-Jul-17 Product Manager $82,700
Female High School Diploma 30-Mar-22 Production Operator $38,200
Male Diploma 4-Sep-19 Packaging Associate $32,600
Male Bachelor's Degree 19-Jun-18 Research Scientist $76,800
Male Master's Degree 7-Jan-21 Marketing Specialist $62,300
Male Bachelor's Degree 25-Aug-20 Sales Representative $46,200
Female High School Diploma 12-Feb-23 Packaging Associate $32,500
Female Diploma 1-Nov-22 Quality Control $43,100
Female Bachelor's Degree 16-Apr-19 Research Analyst $60,000
Male Master's Degree 9-Oct-18 Product Manager $82,100
Male High School Diploma 26-May-23 Production Operator $35,900
Male Diploma 13-Jan-20 Packaging Associate $31,100
Male Bachelor's Degree 27-Jul-18 Marketing Manager $74,200
Male Master's Degree 14-Mar-19 Research Analyst $57,100
Male High School Diploma 28-Sep-22 Packaging Associate $35,200
Male Diploma 11-Apr-21 Quality Control $42,800
Male Bachelor's Degree 26-Jan-20 Sales Representative $46,900
Age
26
32.7
34.3
29.6
30.3
37.7
31.8
35.9
31
31.1
30.3
35
28.5
52.4
35.4
29.8
32.3
31.1
33.5
31.1
31.5
32.7
33.8
30.1
63.7
33.8
28.8
32.7
29.9
27
30.6
28.7
33.7
31.2
34.9
