In this case, you have to write a select, which is the 80% of what you need to know in SQL anyway. They’re not fully fledged queries, but it does include a pretty important subset of the SQL language (the API visualisation language). It means you can write SQL queries on a Google Sheet, using it as a makeshift database!
The =query() function is one of the things that makes Google Sheets so damn special. Method two of doing an XLOOKUP in Google Sheets: QUERY In short, using filter makes you start thinking about Google Sheets very differently. Return a list, and then sort it or do whatever you want with it.Make more sophisticated conditions (like if the text includes something or starts with something).Make conditions based on multiple variables.In fact, you can do much more sophisticated stuff. “Give me stuff from the return array where this condition (or these conditions) are met.” The way to think about filter is also easy. =filter(return_array, lookup_array=lookup_value) But if you will only ever have a 1:1 match, then you’ll only get one value. The =filter() formula is one of those things that makes Google Sheets special: it makes it really easy to think in arrays.įilter is also the best equivalent to XLOOKUP in Google Sheets. It’s easy to read, succinct, and quick.įilter actually returns an array of values. XLOOKUP equivalents in Google Sheets Method one of doing XLOOKUP in Google Sheets: FILTER Here’s a handy Google Sheet with examples of the equivalents of XLOOKUP, fully implemented. lookup_array = the list of country names.=XLOOKUP(lookup_value, lookup_array, return_array) The baseline formula we’ll try to replace is: How XLOOKUP works, and what we’ll try to replace in Google Sheets There are two main ways you can replace XLOOKUP in Google Sheets: These are more specific than SUMIFS or SUMPRODUCT because we’re talking about text strings, not values. For example, the above function would be =INDEX(C2:C11,MATCH(E3, A2:A11,0)). However, it is a little more cumbersome to read. INDEX(MATCH): This is the more elegant solution to looking up data in a table, because you don’t use a column number (which can break).But with VLOOKUP you use a column number in the lookup table, which means when you change the table (which we do all the time!) the formulas break. VLOOKUP/ HLOOKUP: These functions have similar syntax.XLOOKUP is an improvement on two existing equations The most common use case for XLOOKUP in Excel is to look up a value in a table.įor example in the below table, to look up E3 in the range A2:A11 and return a value from C2:C11, you use the function =XLOOKUP(E3, A2:A11, C2:C11). This part is more for Google Sheets users who have been told someone who’s an Excel user “just use XLOOKUP”. You might like to skip ahead if you know this already. Old school methods to avoid (looking at you, VLOOKUP)Ĭontext: What is XLOOKUP and why do you use it?.Method two of doing an XLOOKUP in Google Sheets: QUERY.Method one of doing XLOOKUP in Google Sheets: FILTER.How XLOOKUP works, and what we'll try to replace in Google Sheets.Context: What is XLOOKUP and why do you use it?.