2016 excel: Skip & ignore empty cells in formula

The formula i am using in question is:

=IF(ISNA(MATCH(F16,[FRI.xlsx]Who_Did_What!$C:$C,0)),"",VLOOKUP(F16,[FRI.xlsx]Who_Did_What!$C:$D,2,FALSE))

This is in multiple cells. There is one cell that does not work because the column it is looking at in "[FRI.xlsx]Who_Did_What!$C:$C" has empty cells.

Therefore the cell which has this formula returns "0".

I want the formula to skip/ignore the blank cells and resume the calculation.

The solution needs to be in the formula, or some macro. This is because the sheet with the empty cell "FRI.XLSX" is exported from another piece of software. Also the users of this workbook (where the formula is) are basic excel users and will not have the time to modify MON.XLSX, TUE.XLSX, WED.XLSX and so on.

Hi Thanks for your reply!

I THINK i reattached your spreadsheet with an extra tab (WHO_DID_WHAT), which closely looks like the "FRI.XLSX" i was referring to.

I'm trying to get I16 to find the value that is in F16 in the "WHO_DID_WHAT" sheet, and return the corresponding value in column D.

As you can see i put in gaps and other strange characters this report gives me, this is just like what i am actually working with.

(original i can't attach for data protection reasons)

Hi,

742, next to "TOD", column D, WHO_DID_WHAT sheet

Hello

Thanks for all your solutions

I've tried this on my sheet and it returns 0

If this helps:
I had a search for the value that the formula is looking up, manually, using CTRL+F.
I looked at the first few cells that contain this value, in column C, and D is the column that has empty values.
So i'm guessing if the cell in column D is empty, this is why its returning a '0'

Can i add something to the formula to ignore the empty cells in D? Because it is FINDING things in C but RETURNING empty cells in D.


Thanks so much

I tried your solution,

The example workbook i uploaded uses exactly the same cell numbers and sheet names as the original, so that i can replicate. I've just amended the example sheet to replicate the empty cells in the D column & now it's returning 0 like the original,

So now we have an example just like the original we can look at (see attached).

Your explanation was misleading. Basically, you've wanted the first match that is not blank based on your criteria. The VLOOKUP returned 0 because it found the first match, but that one was empty. Try this:
=INDEX(WHO_DID_WHAT!$D$1:$D$20,MATCH(1,($F16 = WHO_DID_WHAT!$C$2:$C$20)*(WHO_DID_WHAT!$D$1:$D$20<>""),0))
It is an array formula, meaning that you need to use Ctrl+Shift+Enter instead of regular Enter

It works now!!!

I just had to increase the lookup array to cover 9000+ rows of data,

Such a helpful bunch, thanks so much.

Quick question, i intend on updating the files MON.xlsx TUE.xlsx WED.xlsx THU.xlsx etc. by replacing them in the folder they currently are, same name, same location, just different data.
Will this update the formula? Or do i need a refresh macro or something?

Best
Zach