0

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

StillLearningThisStuff
  • 9
  • 4
  • 1
    The "numbers" in the `Prov #` column are text and you are searching for a number `"1" <> 1` You will need to convert the "numbers stored as text" to actual numbers. – Scott Craner Jul 05 '23 at 15:39
  • After updating the whole Prov # column into number type, I am still getting the same error (#272 for example) – StillLearningThisStuff Jul 05 '23 at 18:21
  • You "numbers" are still stored as text. Anywhere you see the green triangle in the upper left corner of the cell has the numbers stored as text. Simple changing the format will not change numbers stored as text to real numbers. – Scott Craner Jul 05 '23 at 18:28
  • Try this: `=FILTER(Providers,IFERROR(--ProvNbr,9999999)=--K3,"")` – Scott Craner Jul 05 '23 at 18:30
  • I apologize for my misunderstanding but it worked, thank you! Appreciate the new info and will pay better attention to that! – StillLearningThisStuff Jul 06 '23 at 16:02

0 Answers0