XLOOKUP Case Sensitivity
By default, XLOOKUP performs a case-insensitive lookup, meaning it finds the first match in a range irrespective of whether it’s uppercase, lowercase or mixed case. To circumvent this, it can be used in conjunction with EXACT.
EXACT checks two text strings to see if they’re the same case. TRUE is returned if they are — FALSE if not.
= 𝙴𝚇𝙰𝙲𝚃 ( 𝚝𝚎𝚡𝚝𝟷 , 𝚝𝚎𝚡𝚝𝟸 )
𝗘𝘅𝗮𝗺𝗽𝗹𝗲
A table called tblColours contains a set of colour names alongside corresponding numbers.
For the Case-Insensitive Lookup, a standard XLOOKUP formula retrieves the number of ‘GOLD’ from tblColours. As ‘Gold’ appears before ‘GOLD’, 3 is returned.
The formula is modified for the Case-Sensitive Lookup. The 𝚕𝚘𝚘𝚔𝚞𝚙_𝚟𝚊𝚕𝚞𝚎 becomes TRUE and 𝚕𝚘𝚘𝚔𝚞𝚙_𝚊𝚛𝚛𝚊𝚢 uses EXACT to compare ‘GOLD’ to each value in the Colour column. This evaluates to:
{ 𝙵𝙰𝙻𝚂𝙴 ; 𝙵𝙰𝙻𝚂𝙴 ; 𝙵𝙰𝙻𝚂𝙴 ; 𝙵𝙰𝙻𝚂𝙴 ; 𝙵𝙰𝙻𝚂𝙴 ; 𝗧𝗥𝗨𝗘 ; 𝙵𝙰𝙻𝚂𝙴 ; 𝙵𝙰𝙻𝚂𝙴 ; 𝙵𝙰𝙻𝚂𝙴 ; 𝙵𝙰𝙻𝚂𝙴 }
The sixth element matches the 𝚕𝚘𝚘𝚔𝚞𝚙_𝚟𝚊𝚕𝚞𝚎 (TRUE), which corresponds to 6 in the 𝚛𝚎𝚝𝚞𝚛𝚗_𝚊𝚛𝚛𝚊𝚢:
{ 𝟷 ; 𝟸 ; 𝟹 ; 𝟺 ; 𝟻 ; 𝟲 ; 𝟽 ; 𝟾 ; 𝟿 ; 𝟷0 }
