Alternative to vlookup to find first non-blank cell

Dave Schierman

Greetings,

I'm using the formula =VLOOKUP([Box Number]@row, {Box Transaction Log Range 1}, 12, false) to show the stock level of an item that's occasionally updated with form entries populate on another sheet. The cell on the reference sheet is not always populated so I'd like to pull the number from the first instance that IS populated.

For example, there may be 8 or 9 entries for Box 1 in the sheet but only one contains a number in the stock level column while all the rest are blank. I need to pull the number. Is that possible?

Thx

D

Kelly Moore

Hey Dave

Each cross reference range in my formula is a single column in your 'Transaction' sheet- the sheet where people are updating the stock levels. I use Index/collect as a lookup when I have multi criteria that must be met. The criteria target the specific stock level to report, looking it up against the [Box Number]@row of your Log sheet. This should find the most recent non-blank stock level of the specific box number.

Mark Cronk

Hi @Dave Schierman ,

I can suggest a workaround. Use a helper column with a formula to rename the box if the stock level is blank or 0. Conduct your vlookup using the helper column as the left most of the range. Vlookup will find the first match which will have stock.

Insert a column to the right of the [Box Number] in your lookup range. Name it [Full Box]. Insert the column formula:

=IF(ISBLANK([Stock Level]@Row), "", IF([Stock Level]@Row =0, "", [Box Number]@row)) Modify it to the actual column names for the stock level and box name in your lookup range sheet.

The column [Full Box] should populate with the box name if the box has stock and be blank if it doesn't.

Adjust your {Box Transaction Log Range 1} range so that the left most column is [Full Box]. Confirm your lookup return column is still 12, or adjust that too.

Work?

Mark

Dave Schierman

Hi @Mark Cronk

Unfortunately that won't work for my purposes. There is other data that we pull off each entry so we can't ju

Mark Cronk

Hi @Dave Schierman ,

Darn. I was hoping you'd be able to use the Full Box column for some lookups and your other ranges when you weren't concerned about stock available. I'll give it some more thought. Lots of very smart people in this community. Someone will help with a solution that meets your needs.

Mark

Dave Schierman

Yea, I'm thinking along the lines of having a helper sheet that would only store rows of data with that field populated and none of the others, then do a vlookup for that field off the helper sheet. Not sure how to accomplish that yet.

Thx

Mark Cronk

Helper columns with column formulas are your friend. They work great to flag rows for a specific purpose or do initial calculations to make other formulas simpler. You can hide them so users don't see them and they do their magic in the background. Think through if helper columns, versus a helper sheet, can get you what you need. Helper sheets are great, and often necessary, but get complicated fast.

Again, good luck.

Mark

Kelly Moore

Hi Dave

Building on @Mark Cronk advice for a helper column in your reference sheet, is it possible to add (if not there already) the system generated auto-number column Row ID? If I understood your needs correctly, I believe you could gather the Stock Level using an Index Collect instead of the VLOOKUP.

=INDEX({your reference sheet Box Stock Level}, MAX(COLLECT({your reference sheet Row ID}, {your reference sheet Box transaction log}, [Box Number]@row, {your reference sheet Box Stock Level}, @cell <> "")))

In this case, the MAX(Collect) gathered is the Row number to be used as the Row_Index part of the INDEX function. The criteria within the Collect pinpoints which Row ID to gather. Since you want the most recent Row ID that meets all the criteria, the max is chosen. I assume when the data comes into your Reference sheet that you add rows to the bottom.

I hope this works for you

Kelly

Dave Schierman

Hi @KDM ,

I'm having trouble grasping your formula.

Lets say the sheet containing the formula is called "Log" and the sheet containing the rows of transactions is called "Transactions". So all 4 of the references in your formula are pulling from the "Transactions" sheet? Is each one setup as a reference of a single column or is one a range and the others columns numbers within that range like in a vlookup?

Kelly Moore

Hey Dave

Each cross reference range in my formula is a single column in your 'Transaction' sheet- the sheet where people are updating the stock levels. I use Index/collect as a lookup when I have multi criteria that must be met. The criteria target the specific stock level to report, looking it up against the [Box Number]@row of your Log sheet. This should find the most recent non-blank stock level of the specific box number.

Dave Schierman

Not sure if this helps to visualize. Assuming top section is the "Log" sheet and bottom section is the "Transaction" sheet and the formula(s) would be in the yellow highlighted fields.

Dave Schierman

https://community.smartsheet.com/discussion/73979/alternative-to-vlookup-to-find-first-non-blank-cell

That did the trick. I used MIN because my forms add to the top but it worked perfect. Thank you so much for the help.