FindRow: A Better VLookup

About VLOOKUP

VLOOKUP, or vertical lookup, is a common function in spreadsheet applications used to find values that may be stored in other parts of your workbook. VLOOKUP vertically searches the first column of a range for a key, and then returns the value of a specified cell in the row found.

VLOOLKUP saves time but, more importantly, it reduces duplicate data and user errors by minimizing manually entered, repeating data.

VLOOKUP example

Here’s a scenario where VLOOLKUP comes in really handy. Imagine you want to find the sales lead, by region, whenever you add a new customer to your lead your sheet, as below.

VLOOKUP_Image

Based on this dataset, your VLOOKUP formula would look something like this:

e9f3e48180a5c8c950c01fdb140596b26281c5c3

Since VLOOKUP will search down the left-most column for the key until it finds a match, to use VLOOKUP the key must be in the left-most column of the target region since. Seems complicated, right?

FINDROW vs. VLOOKUP

You can achieve the same results—and more—in Honeycode using the FINDROW function. Following the same example as above, your FINDROW formula would look like this:

19367194683e5a9121ac4b9375ea8422cda3c7a6

Seems a lot simpler, right? That's because FINDROW works seamlessly with Honeycode tables. FINDROW lets you search across any column or multiple columns and does not depend on the position of the column. :+1:

FINDROW syntax

Let's do a more generic breakdown of the syntax for FINDROW:

FINDROW(TableName, "TableName[ColumnName] = \<value\>")

FINDROW data links

Another cool feature of FINDROW is that it creates a link to the entire row in the target region rather than returning a specific column. By default it will display the left-most column. but you can easily retrieve other columns from the row. Let’s see it in action.

To retrieve the email address of the sales lead, simply enclose the column name in brackets :

FINDROW(Leads, "Leads[Region] = ""West""")[Email]

You can also use references to rowlink cell and extract relevant column using the same syntax:

=C1[Email] where C1 is a cell in your sheet

Since Honeycode supports structured references , another way to write this expression is:

=[Region][Email] Where region is the column containing the findrow() formula

Replace all your VLOOKUPS today and never look back. Happy building!