xlookup using two return array columns

[BeadyBob]

Hello,
Is it possible to do an xlookup using two return array columns? Or, if there is a better solution please let me know.

For my spreadsheet, I have two tabs. I am doing an xlookup on the first tab to find a project number on the second tab, then I want to pull the milestone date from column G (actual date) unless it is blank, then I want it to pull the date from column H (projected date). FYI, sometimes both columns are blank.

Here is my formula, which is lacking sophistication since it's not working. When G has a value, it returns false instead of a date. When H has a value the formula returns the date.
=IF(XLOOKUP([@Project],Export!$A$2:$A$100,Export!$G$2:$G$100)=0,XLOOKUP([@Project],Export!$A$2:$A$100,Export!$H$2:$H$100))

Thank you, I appreciate any help I can get.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[BeadyBob]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you for the tip, I just updated. I'm on 365.

 

[Fluff]

Thanks for that, could you move the formula outside of the table? That way you could use

=LET(x,XLOOKUP([@Project],Export!$A$2:$A$100,Export!$G$2:$H$100),IF(INDEX(x,,1)=0,"",x))

(Changing the [@Project] to a cell reference) & that way it will spill across.

 

[BeadyBob]

I really appreciate the suggestion. It seems to work great when there is a date found in column G, but if G is blank it isn't pulling the date in H, it instead shows blank.
I converted the table back to a range, pasted the formula, and changed [@Project] to the cell reference. The result is good if a date is found in column G, but when G is blank it is not returning the date found in column H. If you have any suggestions to resolve, or can tell that I have done something incorrect please let me know. Very grateful for the help!

 

[Fluff]

Will either G or H have a date, or could they both be blank?

 

[BeadyBob]

Both G and H could be blank at times.

 

[Fluff]

Ok, how about

=LET(x,XLOOKUP([@Project],Export!$A$2:$A$100,Export!$G$2:$H$100,0),IF(INDEX(x,,1)<>"",INDEX(x,,1),IF(INDEX(x,,2)<>"",INDEX(x,,2),"")))

 

[BeadyBob]

There it is! Thank you so much, that works perfectly. You've saved me so much time, I truly appreciate it!

 

[Fluff]

You're welcome & thanks for the feedback.