Removing the last blank line from an Xlookup.

[DazCD]

Good morning all,

As the title says, I have a a blank line that sometimes returns at the last row due to the difference of the amount of items a gift pack may contain. (I've tried to upload the mini book but can't seem to get it to work so I'll have to post images)

If the Gift pack contains 5 components, naturally the 5th row will reference data. However, if the gift pack has 4 components, you can see from the image that the low row is "0" & "#N/A", respectively. I've tried using the FILTER function but FILTER still evades me as to how it actually works.

=IF($A$2="","",XLOOKUP(A3,'F:\Quality Control\Ingredient Listings\[CD Ingredient List (EFACS).xlsx]Finished'!$A:$A,'F:\Quality Control\Ingredient Listings\[CD Ingredient List (EFACS).xlsx]Finished'!$B:$B))

The above formula is what i'm using to pull those components codes.

Additionally, is there any way to shorten or make the formulas easier to read rather than having it spammed with "F:\Quality Control\Ingredient Listings\[CD Ingredient List (EFACS).xlsx]Finished'!$A:$A" ?

Thank you for any replies, have a nice day!

 

[StephenCrump]

Presumably it's the XLOOKUP that's returning the #N/A? XLOOKUP has an optional if_not_found argument that you could set to "" if you wish.

It's not clear from the question what's causing the 0 value, or whether you want to suppress it?

And it's also not clear why you think FILTER might be appropriate?

 

[DazCD]

Apologies, I overlooked the [If not found], in the XLOOKUP.

Fixed it. thank you.

 

[StephenCrump]

No problem. Glad you got it working.