Forum Discussion

stillwatergirl's avatar
stillwatergirl
Copper Contributor
Jun 06, 2025

Xlookup Displays Previous Match Results When Blank

Hi, I created an Xlookup formula that looks for a match in cell B1 of my spreadsheet (see image below), returning data from other sheets in my workbook. The formula functions perfectly when there is a number in B1, by returning "No Match Found" if the number I enter isn't in the lookup range, but if I delete the number and leave cell B1 blank, Excel displays a previous match -- and not even the last match, but a match from sometime early in the process. It doesn't matter how many other numbers I enter, delete, enter, and delete into that cell, the same previous match pops up when B1 is empty.

I should also note that this is happening with the formujlas in columns C and D, but as you can see below, the cell in column B is blank, even though it contains the same XLOOKUP formula - maybe because that cell needs to match B1 and the others are pulling from different columns?

Here is my formula: =XLOOKUP($B$1, 'SheetName' !$A$8:$A$1000, 'SheetName' !F$8$:$F$1000,"No Match Found",0). I hope someone can tell me how to return a blank cell when the criteria cell is blank, because I've never had this happen before and have spent way too much time on this issue.

 

excel
office 365

1 Reply

Sort By
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jun 07, 2025

    I suspect you have a blank cell in  'SheetName' !$A$8:$A$1000 and that is what it is finding.

    a simple bandaid is 

    =IF($B$1="","",XLOOKUP($B$1, 'SheetName' !$A$8:$A$1000, 'SheetName' !F$8$:$F$1000,"No Match Found",0))

    but better yet is to make sure your data is 'good' and no blanks and instead of using fixed ranges like 'SheetName' !$A$8:$A$1000 which I assume is the first column of a table to 'Format as Table' (Home->Format as Table) and then NAME that table (once formatted you will see the Table menu and you can change the name of the Table.  and then use table Structured Format like if the first column header is 'ID' then Table1[ID] will reference the entire column of ID data no more, no less and grow (or shrink) as the Table does.

Resources