Search code examples
google-sheetsformulaindex-match

How to return the first non-error value (#N/A) in index match?

I have two tabs in my Gsheet and I am trying to return a value from a specific column in one tab to a cell in the other tab but the column includes multiple #N/A. Therefore, the return will turn up blank as it will not pick up the correct input by default.

For example, the formula in the cell that I am trying to return to is INDEX('Tab 1'!$A:$A,MATCH($B4,'Tab 1'!$B:$B,0),1). The value in Column A I am looking for is the value that doesn't say #N/A but with this formula it's not allowing me to return the first non-error value. Instead I get a blank.

How can I write the formula in a way that it picks up the first value after #N/A? Thank you in advance

Solution

  • Can you try:

    =+filter('Tab 1'!A:A,'Tab 1'!B:B=B4,not(iserror('Tab 1'!A:A)))