17

In XLOOKUP, the ability to use if_not_found is great, however, what might I do if the value is found, but the result is blank? For example:

A:Subnet     B:Description
10.0.1.0/24  Basement
10.0.2.0/24
10.0.3.0/24  Upstairs

I want XLOOKUP("10.0.2.0/24",$A:$A,$B:$B,"",0) to return a blank, but since the search text is found (but there's nothing in the result), the result displays a 0. I want it to be blank if either the search text is not found or if the resultant set is empty. Neither my tinkering nor my Googling has not borne fruit... thoughts?

4
  • 10
    add &"" to the end. May 14, 2020 at 17:07
  • or a space in the empty cell...
    – gns100
    May 14, 2020 at 18:44
  • @ScottCraner Thanks Scott... that's the answer!
    – theglossy1
    May 14, 2020 at 19:07
  • There is a lengthy discussion here with same subject
    – MyICQ
    Jun 12, 2023 at 8:38

1 Answer 1

24

Add &"" after the formula. For example:

XLOOKUP(C3,B6:B10,E6:E10)&""

(@Scott Craner's helpful comment formatted as an answer)

2
  • So simple! Never thought of doing this before - thank you (Scott too :)
    – Andi Mohr
    Nov 13, 2021 at 19:09
  • 2
    But this change result type to string and can not be used for numerical functions such as AVERAGE. May 18, 2022 at 4:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .