LOOKUP function return value

Hi Smartsheet team,

Thanks for getting the much awaited LOOKUP function supported in Smartsheet.. This is a great add !!

I see a problem with LOOKUP() return value when the lookup_value is not found in lookup_range. It is returning Empty string instead of an Error.

Because of this, I am not able to distinguish between key not found and empty index column value. This is a big limitation to LOOKUP function as of now.

I hope this issue will be fixed soon.

Thanks

-George

Note: Excel returns #NA Error when lookup value is not found in the lookup range.

Find more posts tagged with

Comments

Taylor F

Hi George,

We choose not to display an error when the requested result isn't found in the LOOKUP table or provide false values. In Excel, the VLOOKUP function will produce the last value of the table which isn't helpful in most cases.

By using the LOOKUP function in an IF formula you can display a custom error message if the returned value is empty.

For example:

=IF(ISBLANK(LOOKUP(lookup_value, lookup_range, 2)), "Not Found", LOOKUP(lookup_value, lookup_range, 2))

George Thomas Irimben

Thanks for the reply!

In Excel, VLOOKUP function returns/prints #N/A if the lookup value is not found in the lookup_range. Pls double check at your end.

The workaround mentioned above doesnt resolve the issue because, there can be "empty" return values even with lookup_value found in the lookup_range.

Can we get #NOT FOUND ERROR in these scenarios so that we can use ISERROR() or IFERROR() to act accordingly..

Thanks

-George

Taylor F

Hi George,

Excel won't produce an error if you leave off the "Exact Match" or set it to true. By setting the VLOOKUP to false you are forcing a match to be found in the table and if there isn't a match it will produce the #N/A error.

We won't produce this error if there isn't an exact match found so you will need to use the IF formula workaround for now. I've submitted your feedback to our enhancment request list to add an error to LOOKUP if there isn't an exact match found.

Gregory

Hi,

I'm having a similar issue with Lookup.

When I look up one value in a range, I get the correct result, but when I look up a different value in the same range, I get a blank. For example if I look up "grego" I get Doc20, if I look up "doclisa" I get a blank. Am I missing something?

Taylor F

Hi Gregory,

First, the LOOKUP function will provide the first value that is finds in the table. It will start from the top fo the table and scan down until a match. If the table is out of alphanumeric order, it won't always find the match you are looking for.

Try placing the word false in the fourth argument of your formula.

For example:

=LOOKUP('doclisa", [2]1:[Column2]20, 2, false)

This will force LOOKUP to locate an Exact Match.

The other option it to sort the rows in the table to be in alphanumeric order.

Gregory

That worked like a charm! Now we just need to be able to reference different sheets!

Tim E

Hello,

Adding the false arguement worked for me, but wondering if I am trying to have the formula lookup the last entry for that match? I am using a WebForm to add new entries and want have to have summary of sorts at the top with the info from the last matching entry?

Thanks,

Tim E

Daniel Stein

Hi all,

We are considering changing the return value of LOOKUP from the empty string to #NO MATCH if the 4th argument is false (e.g. "exact match" is required) and there is no match. This will allow you to distinguish between "no match" and "blank."

Please let us know if you have any input on this change. Results of some formulas could change as a result, but our sense is that this change would still be a good one overall.

Thanks!

Daniel Stein

Product Manager, Smartsheet

J. Craig Williams

Daniel,

Have you considered an additional optional argument that will provide the user the ability to define the #No Match result?

This would leave all existing formulas returning the same current value (blank or empty string) but give you the function that I need and you are proposing.

Either way, yes please.

Craig

George Thomas Irimben

Hi Daniel,

Thank you for prioritizing this request!

One clarification...Will proposed #NO MATCH return as an error so that I can engage IFERROR() function to catch the Not found scenario and act accordingly? e.g. =IFERROR(LOOKUP(.....),"Not found in the list")

Or, #NO MATCH return will be simply a text return on which I need use IF() and string compare with "#NO MATCH" ?

Cheers!

George

Daniel Stein

Thanks J. Craig and George for your input.

@George, to answer your question, #NO MATCH would indeed be an error code and work with IFERROR and ISERROR. We would change the error code returned by the MATCH function from #NA RESULT to #NO MATCH while we are at it, just to make it easier to understand.

Best regards,

Daniel

qgarrett

Anyone have tips on look up tables that shift when a new row is added and the formula is copied or dragged down? The formula doesn't autofill for new rows. I need the same look up table but new row search value.

I need the other column data to autofill for filtering purposes when the data is filtered to PowerBI.

Screen Shot 2018-01-01 at 8.04.32 PM.png

qgarrett

The second screenshot didn't add.

Screen Shot 2018-01-01 at 8.04.41 PM.png

Taylor F

Hello qgarrett,

Using the dollar symbol in front of the row numbers for the lookup table will force it to keep the absolute value as you drag-fill down the sheet.

=LOOKUP(Column6, Column$2:[Column3]$4, 2, true)

-Taylor

qgarrett

Oh. Em. Gee. You just saved my whole project! Thanks so much for your help. Crazy how one little $ fixed everything!