XLOOKUP - How to Exclude Blanks Altogether and Return Matches (Consolidate List)

[excelnewbie10101]

Hello! Wondering if anyone can help... I've been stuck trying to figure out how to exclude blanks with an XLOOKUP. I've tried many different variations of formulas, but nothing seems to achieve what I want. I am trying to have one single consolidated list based on the data with no blanks pulled in. Is there a way to get the list to shrink down to only show what has data? Please see screenshots attached.

sample test for xlookup.xlsx
	J
8	apple
Sheet1

Cell Formulas
Range	Formula
J8:J13	J8	=XLOOKUP(J4,B3:F3,B4:F9)&""
Dynamic array formulas.

 

[hagia_sofia]

Generally speaking you could probably e.g. use FILTER function to exclude cells without values but could you please upload more of your sample data?