Re: How to match a text value to a number value?

The ONLY solution I've found for this is to suffix an alpha character
to all values in the lookup list and reference list. Then, finally,
Excel sees it all as text (of course). Naturally, I do this in a new
column as not to muff up my original data. The MATCH function then
works using these new columns.

But still and all... is this the most professional method of dealing
with this?



On Nov 21, 1:35 pm, c mateland <chuckmatel...@xxxxxxxxx> wrote:
Excel 2003

[lookup value = string I'm searching for]
[reference table = list I'm searching in]

I'm looping through a list of lookup values and matching them to
entries in a reference table. All I want is a true/false if the lookup
value exists in the reference table.

When performing a vlookup or match in vba, Excel sees the lookup value
as text data type but sees the reference table as a number data type,
and therefore doesn't make the match. It only has this issue when the
lookup value contains no alpha-charaters. For example, the lookup
value is 52123 and in the reference table is 52123, yet I can't get
Excel to make the match. But if the lookup value contains alpha-
characters, such as X52123, then it matches fine to X52123 in the
reference table.

) The lookup values were exported into Excel via some database
program, and the reference table was exported into Excel from some
other database program.

) All cells are formatted as text.

) I gave up on CountIf also because it couldn't tell the difference
between 052123 and 52123.

Any ideas on how I can get this right?

.

Relevant Pages

  • Re: Managing multiple instances
    ... That will probably mean that you have to have some sort of lookup table somewhere in the implementation. ... You will also need some way to describe the client context so that it can be mapped to the actual object identity (e.g., an index into the lookup table that yields a reference). ... All you have to provide is an enumeration variable that is named for the models. ... Unless instantiation is trivial and unlikely to become more complicated, it is generally good practice to encapsulate the rules and policies for instantiation away from the rules and policies of collaboration. ...
    (comp.object)
  • How to match a text value to a number value?
    ... [lookup value = string I'm searching for] ... I'm looping through a list of lookup values and matching them to ... entries in a reference table. ... When performing a vlookup or match in vba, Excel sees the lookup value ...
    (microsoft.public.excel.programming)
  • Re: last number array from string
    ... Only when searching for values equal to or less than the largest value ... with a reference to the last value in the range or array searched. ... >erratic nature of LOOKUP with unsorted data is anything to go by, ... given how Excel works now (all versions at ...
    (microsoft.public.excel.worksheet.functions)
  • RE: How to match a text value to a number value?
    ... [lookup value = string I'm searching for] ... entries in a reference table. ... Excel to make the match. ...
    (microsoft.public.excel.programming)
  • Re: How to match a text value to a number value?
    ... your lookup cell was either text or numeric value. ... is forcing your reference table to all text values using XL's TextToColumns ... as text data type but sees the reference table as a number data type, ...
    (microsoft.public.excel.programming)
Quantcast