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
Can you try:
=+filter('Tab 1'!A:A,'Tab 1'!B:B=B4,not(iserror('Tab 1'!A:A)))