XLookup -or- Countif with leading numbers then Character

[cjmueller08]

I keep running into this problem while trying to perform xlookups from system outputted data in a .csv format.

Scenario is I have a column that changes total numbers and also will and will not have a character at the end. I have the same data in both columns I'm trying to extract a xlookup value from, but I cannot get the two to talk.

Example of data (would be present in the lookup column, and array column)

1530 - Match
1555W - Match
16990 - NO Match
16990W - Match

I've tried helper columns but due to the 16990 (no match) and 16990W (match) I can't do much character extraction for multiple Xlookup's or countifs.

Any insight into this would be appreciated.

 

[Alex Blakenburg]

What version of Excel are you using ?
What is the formula you are using ?
If you change the column to Currency do both the 1530 AND the 16990 change ?
Also show us the data layout if you are using a picture include the Row and Column references.