Cara menggunakan google sheets run function

This XLOOKUP in Google Sheets tutorial is suitable for users of Google Sheets

OBJECTIVE

Use the equivalent functions in Google Sheets to perform an Excel XLOOKUP on a dataset.

In this guide, I’ll cover

XLOOKUP IN GOOGLE SHEETS EXPLAINED

XLOOKUP does not (yet!) exist in Google Sheets. XLOOKUP is a new, modern replacement for older lookup functions in Excel, such as VLOOKUP and INDEX MATCH, but it doesn’t exist in Google Sheets in the same way other lookup functions do.

Using XLOOKUP in Excel, you can find values in a table or range by row and return the corresponding value from another column.

In the screenshot below, we have used XLOOKUP in Excel to return the employee’s extension number in cell F4.

The lookup_value is the value in cell F4. The lookup_array is the range where the lookup value is found, A5:A15. The return_array is the range that contains the value you would like to return, C5:C15.

This handy and flexible lookup formula is great for Excel users. Unfortunately, XLOOKUP is not currently available in Google Sheets at the time of writing this blog. So, what is a viable alternative in Google Sheets?

Well, the most common use of XLOOKUP is to look up a value in a table. With that in mind, there are four different ways you can produce a similar result: INDEX/MATCH, VLOOKUP, FILTER, and QUERY.

XLOOKUP IN GOOGLE SHEETS VIDEO TUTORIAL

REPLACING XLOOKUP IN GOOGLE SHEETS

In the following examples, we will be taking the basic arguments in XLOOKUP (not the optional arguments) and we’ll try to replace them:

=XLOOKUP(lookup_value,lookup_array,return_array)

METHOD 1: USING INDEX/MATCH

Combine the INDEX and MATCH functions in Google Sheets.

Start by indexing the range the contains the value you would like to return, in this case, the extension number. Then use the MATCH function to return the correct row using the lookup value in cell F3.

Add the FALSE or 0 arguments to the end to exactly match the name in cell F3 to the name in the table.

METHOD 2:  USING VLOOKUP

VLOOKUP in Google Sheets is very similar to VLOOKUP in Excel. Whilst XLOOKUP is an improvement to VLOOKUP. This trusty formula still gets the job done.

Start by specifying which value you want to look up in the table, cell F3. Next, select all of the data, A4:C14. Specify the column number that you would like to return. For us, we want to return the extension number, which is in column number 3.

Note: VLOOKUP numbers columns from left to right.

Finish off the formula with a FALSE argument to exactly match the employee name in the table.

METHOD 3:  USING QUERY

QUERY is one of the few functions that are available in Google Sheets and not in Excel. You can use QUERY to perform a lookup, but bear in mind this is a more sophisticated function and not as simple as some of the examples we have already seen.

The QUERY function enables you to write SQL queries on a Google Sheet to make things happen. Due to this, the way you write the QUERY formula to perform a look-up is different from the normal ‘formula language’ you might be used to using.

First, you need to specify the entire range of data, cell A2:C12. Next, you need to provide a SELECT statement to specify the column you would like to return, column C. Finally, you need to provide the query with the criteria, return column C when column A is equal to the value in cell F2.

This is fairly straightforward, but you must use single quotes to surround text strings and break the query as you reference the cell that contains the lookup value.

METHOD 4:  USING FILTER

In my opinion, the fastest, easiest, and closest formula to XLOOKUP is the FILTER function in Google Sheets.

Simply, specify the range that contains the value you would like to return, C2:C12. Then specify the criteria range, A2:A12 and then finally the criteria, F2.

Fast, simple and effective!

For more example and guidance on how to use functions and formulas in Google Sheets, please check out the following links below:

Ben Collins – 18 Google Sheets Formulas Tips & Techniques You Should Know

Envatotuts – How to Add Formulas & Functions in Google Spreadsheets

CIFL – 9 Google Sheets Formulas to Master

Take a look at more, free Excel training on Simon Sez IT.

Deborah Ashby

Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.