Match Formula

[jstevens]

Is it possible to use a MATCH formula similar to this? Column B may contain both text and numeric values. The formula should look in Column B for text i.e. values would be treated like text.

Cell A1 = '3333333
Cell C1 Formula =MATCH(A1,STRING(B:B),0)

[Rudi]

Not sure if this is the best solution, but try this array formula:

=MATCH(A1,TEXT(B1:B10,"@"),0)

Note:
Confirm with CTRL+SHIFT+ENTER (an array formula)
I would not recommend using a reference to the entire column (B:B) if using array formulas

[jstevens]

Rudi,

Thanks for the suggestion. It worked.

What purpose does the "@" serve?

[Rudi]

Hi John,

Its a placeholder for text, or in other words, the @ sign represents that the value you provide in the formula be converted to text.

If you consider the number format #.00, the # sign is a place for insignificant numbers (a representation of any amount of digits) and the zero is a placeholder for significant numbers (a representation of one digit per zero (or two decimal places in the example)). Anyways... I'm just trying to point that like the # and the 0, the @ sign represents text.