EXCEL - Error with Filter/Index Match/Xlookup/Vlookup - partial results

Question

ISSUE

I think I am missing something super simple but I have tried the methods above to try and return a single result (a provider name) given an associated provider # (which is really a text value in the cells to allow preceding zeros) and am not getting results for ALL the rows/am getting #NA for certain rows.

Img of table with no result (provider # search)

Img breakdown:

• Column I - Provider #
• Column J - associated provider name
• "K3" - search/keyword
• "K4" - resulting match/filter
• red text - tried formulas

So I have a total of 403 rows for Col I & J and am getting blanks/#NA on certain rows for an unknown reason. The screenshot above shows how provider #272 doesn't populate a provider name (should be Abraham Lincoln Memorial Hospital) but the next how ever many rows do just fine.

---

PREV RESULTS

The thing is, this lookup/filtering was working when the criteria was originally provider name instead of #. I was getting ALL rows back with the assoc. provider #.

Img of table with result (provider name search)

---

TESTED

So I have tried the FILTER, XLOOKUP, INDEX MATCH, & VLOOKUP functions without prevail. I am unable to identify why this is happening given I just changed around the columns to search through and match to a cell value/keyword.

I have tested this as a table and a range with the same results. I have also moved around the columns so instead of Prov Name (Col I ) | Prov # (Col J) it was Prov # (Col I) | Prov Name (Col J) but either way they are and always will be adjacent.

---

ADDTL CONTEXT

This functioning happens in a worksheet (called "Admin") but is connected/communicates to a Userform before/after this function:

• The userform has a dropdown where a user selects the provider #
• The value gets inputted into the ws
• The value is then worked thru via the excel function to populate the assoc. provider name
• That value gets read back into the form

Private Sub ProviderListIP_Change()

    'input into admin ws so prov list updates
    Sheets("Admin").Range("K2").Value = ProviderListIP.text
    
    'read back assoc. prov # into form
    'Me.IPtxtProviderName.value = Sheets("Admin").Range("K4").value
    'Me.IPtxtProviderName.text = Sheets("Admin").Range("K4").text   'both not populating full list from # search
       
End Sub

Again, this was working fine when it was reading through the prov names and needing to populate the assoc. #, but now switched around it's not working.

Img of form with also no result (provider # search)

---

UPDATE

I updated the Prov # column/range into number type and am still getting issue.

1. FILTER(Providers,ProvNbr=K3,"")
2. XLOOKUP(K2,ProvNbr,Providers,"")
3. INDEX(Providers,MATCH(K3,ProvNbr,0))
4. VLOOKUP(K3,I2:J403,2,FALSE)

IMG