XLOOKUP--How to Ignore Blanks in Return Array

[savery2151]

Hi--hoping someone can help me. I'll try to explain my issue as best I can.

The formula below is returning a '0'. In the formula, the lookup value below is in the return array data 3 times (3 rows). The first and third rows are blank, but there is data I need in the second.

I think the reason it's returning a '0' is that it's grabbing the first match of the lookup value in the return array rows.

If that's the case (not sure if it is), is there a way to get to ignore the blanks and return the value in row 2?? Thank you in advance!!

=XLOOKUP(D13,AX:AX,AZ:AZ)

 

[6StringJazzer]

You may want to update your profile to show your Excel version. This solution will work in later versions:

=XLOOKUP(D13,FILTER(AX:AX,AZ:AZ<>""),FILTER(AZ:AZ,AZ:AZ<>""))

 

[savery2151]

Thanks so much!! I will try it now. And thanks for the feedback on the version. I really appreciate your help!!!

 

[Alex Blakenburg]

This should work too.

=XLOOKUP(1,(AX:AX=D13)*(AZ:AZ<>""),AZ:AZ)

 

[savery2151]

This should work too.

=XLOOKUP(1,(AX:AX=D13)*(AZ:AZ<>""),AZ:AZ)

Belated thank you!