VLOOKUP

[Excel User]

Spreadsheet received has a green tick mark (`) in the
upper left hand corner of each cell on the worksheet. The
VLOOKUP function would not work with the green tick mark
in the cell.

Deleting the green tick mark did not work. What I tried
that did work was to place a value of '1' in the cell and
did a copy / paste special / multiply.

Because the spreadsheet is so huge I was wondering if
there is a smarter way of doing this.

 

[Guest]

Hi ExcelUser
What do you mean by "would not work"

If the multiply by one works, you can copy the cell with the 1, then select the entire range that contains the offending VLOOKUPs and do Edit>Goto>Special>Constants, or Edit>Goto>Special>Formulas, whichever one selects the right cells. Then do the paste special

Good Luck
Mark Graesse
(e-mail address removed)
Boston MA
----- Excel User wrote: ----

Spreadsheet received has a green tick mark (`) in the
upper left hand corner of each cell on the worksheet. The
VLOOKUP function would not work with the green tick mark
in the cell.

Deleting the green tick mark did not work. What I tried
that did work was to place a value of '1' in the cell and
did a copy / paste special / multiply

Because the spreadsheet is so huge I was wondering if
there is a smarter way of doing this

 

[ExcelUser]

Mark, I hope this explains the condition better:

The tick marks I am referring to look very much like the
tick mark seen for a Comment. These tick marks appear
in the left upper corner of each cell on the spreasheet.

The vlookup (in this case) matches information (General)on
Sheet1 to Sheet2. The format of the data is the same on
both Sheets. I know there are matches! What I get is
#N/A. However, when I write over the existing values
vlookup does work.

When copy / paste special (values only) is used, vlookup
still does not find the matching values. It appears that
the tick marks are imbedded in the Excel spreadsheet.

An interesting observation:

We copied the Excel spreadhseet to a text file and then
imported the data back to Excel. The tick marks did not
appear on the txt! But they did appear again in Excel when
the txt file was imported.

-----Original Message-----
Hi ExcelUser,
What do you mean by "would not work"?

If the multiply by one works, you can copy the cell with

the 1, then select the entire range that contains the
offending VLOOKUPs and do Edit>Goto>Special>Constants, or
Edit>Goto>Special>Formulas, whichever one selects the
right cells. Then do the paste special.

 

[Guest]

Hi Again,
I'm not familiar with the green tick marks. I use Excel 97 and 2000, so I assume these are in a newer version.

From what you describe it definetly sounds like the lookup value and the data in the lookup tables are formatted differently. If you think the lookup value is text, while the table is number, then try changing your VLOOKUP to:

=VLOOKUP(VALUE(look_up_value),look_up_table,return_column,0)

This will convert the look_up_value to a number before it checks the table.

If you think the lookup value is a number while the table data is text, then copy a blank cell, select the lookup table data, and Paste_Special>Add. This should convert the data to numbers.

If you are trying to Copy the existing data, and then Paste_Special>Values, since the data is text to begin with it still paste back in as text. The functionality is mostly used to convert formulas to hard values.

Let me know what happens.

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- ExcelUser wrote: -----

Mark, I hope this explains the condition better:

The tick marks I am referring to look very much like the
tick mark seen for a Comment. These tick marks appear
in the left upper corner of each cell on the spreasheet.

The vlookup (in this case) matches information (General)on
Sheet1 to Sheet2. The format of the data is the same on
both Sheets. I know there are matches! What I get is
#N/A. However, when I write over the existing values
vlookup does work.

When copy / paste special (values only) is used, vlookup
still does not find the matching values. It appears that
the tick marks are imbedded in the Excel spreadsheet.

An interesting observation:

We copied the Excel spreadhseet to a text file and then
imported the data back to Excel. The tick marks did not
appear on the txt! But they did appear again in Excel when
the txt file was imported.

-----Original Message-----
Hi ExcelUser,
What do you mean by "would not work"?

the 1, then select the entire range that contains the
offending VLOOKUPs and do Edit>Goto>Special>Constants, or
Edit>Goto>Special>Formulas, whichever one selects the
right cells. Then do the paste special.