INDEX MATCH MATCH where the cell is not blank

mikor

Hello,

I have a unique situation where I cannot do a standard INDEX MATCH MATCH. We have a spreadsheet like the simplified version attached. I'm trying to grab the data where the name matches on the left and the date matches on the top. However, since there are multiples of the same name on the left, I only want to grab the cell that is not blank. Additionally, the dates at the top are dynamic, based on a date pulldown elsewhere.

The formula I started with is something along the lines of:

=INDEX([1]4:[3]8, MATCH({Name}, Name4:Name8, 0), MATCH({Date}, [1]3:[3]3, 0))

But that returns blank as the first instance of Mike and Sept-21 is blank.

I couldn't figure out a way to use INDEX COLLECT either. Any help would be much appreciated!

Eric Law

@mikor You really do need a unique identifier when dealing with INDEX/MATCH or VLOOKUP, otherwise you will run into the problem that you currently are getting.

Another issue you are going to run into while making the formula is that your parameters will constantly be changing as you will continuously be creating more columns, and you will eventually reach a point where smartsheet will not be able to run the formula because there are too many cells to evaluate.

The best thing to do is to rethink your strategy on populating a sheet. Remember, SS is more akin to a database than it is to an excel sheet.

mikor

Understood and thank you for the response. I was able to get it working by using an INDEX/COLLECT within the same sheet lower down in order to organize the rows into unique identifiers that I could INDEX/MATCH/MATCH from a remote sheet.

I guess what I keep running into is that while SS is more like a database than Excel, it doesn't return an array of items that you can select from or things like returning the Nth MATCH (limited to only the first match) and does not allow iterations like other languages that work with databases (FOR/WHILE loops or IF(i=0; i<10; i++). I'm slowly learning my way into thinking like a smartsheet and will get there eventually.

Thanks again for the response. The community posts have been extremely helpful!

Félix Landry

Hi , I am interesting in a solution, I have the same situation.

Thank you !

mikor

@Félix Landry Below are the screenshots of the test sheet I set up. Please note, this does NOT work if you have multiple entries for a person in the same column as it will only grab the first entry instance and pull the data below.

Basically, on the same sheet, I set up an area below my data entry (area below orange row) that first collects the unique name identifiers in the DATANAME column based on the DATAROW number.

Then on the date columns below the orange row, I do an INDEX(COLLECT()) to compile all of the data into a single row with the name listed from DATANAME. I also have the rows below the orange line as children so that I can collapse them on the sheet so they are not visible except when I am trying to link to a separate sheet.

I use this compiled data to link to a separate sheet with an INDEX(MATCH(MATCH())) for workload projections. Let me know if you have additional questions and I can try to answer them.