I am trying to figure out a formula to find the first non blank cell with data on a separate tab from where I need the value returned. I've tried the following formula but no luck: =INDEX(Sheet2!H:H,MATCH(1,Sheet2!A:A=Sheet1!A3)*Sheet2!H:H<>"",0)

On Sheet 1 under column "Value from Sheet 2" I need the value from Sheet 2 under column H that is not blank using Sheet 1's "Name" column as the reference, in this case value "123" is the first non blank cell for name "ABC" on Sheet 2.

Thank you

CodePudding user response：

In your data above, =sheet2!A$3:A$6=A3 is going to return {true;false;false;true}.

Further, =NOT(ISBLANK(sheet2!H$3:H$6)) is going to return {false;true;true;true}.

Thus, =(sheet2!A$3:A$6=A3) * (NOT(ISBLANK(sheet2!H$3:H$6))) should get you the array {0;0;0;1}, which is what you want to lead you to desired cell.

=MATCH(1, (sheet2!A$3:A$6=A3) * (NOT(ISBLANK(sheet2!H$3:H$6)))) gets you the index to the first hit, which is 4, and using that as in index into H$3:H$6 gets you all the way home.

But I’d exploit the more useful features of XLOOKUP instead and I resort to index/match only when required—which is pretty rare now that XLOOKUP exists:

=XLOOKUP( 1, (sheet2!A$3:A$6=A3) * 
(NOT(ISBLANK(sheet2!H$3:H$6)))), sheet2!H$3:H$6)

One fewer functions, AND it adds the ability to handle a not-found condition.

This all should still work if you just decided to use A:A and H:H as in your previous formula.