vlookup error with format cell type

[busybeemg]

I am working with excel 2007. I have a vlookup function that is pulling info from a different sheet within the same workbook. the Vlookup function is pulling looking up numeric and alphanumeric data and then populating my cell with its associated name. But there seems to be an issue when the vlookup is pulling the numeric data. So I changed the formatting of the cells to various categories, this works for some and not others. I need the vlookup to work for all regardless of the cell format.
Please help

Thanks

[martindwilson]

sample data would help and in a workbook!

[shg]

I am working with excel 2007.

Would you please change your profile to show that?

[busybeemg]

Data is as such:
These are in the worksheet "Org Codes" column A and Column B respectively
3h21200000 ETS
5517000000 ESH
17F0000000 PMG
5512000000 IPG

Cell C4 is where people enter in a 10-digit code (the ones above) it is formated as Text
I have a vlookup in cell C5 that states: =VLOOKUP(C4,'Org Codes'!$A$2:$B$3000,2,FALSE)

So when people enter in an alphanumeric code it works fine, but if they enter a numeric code it doesnot work. I have tried to change the format of cell C4 to general but that does not work, then to numeric and it works for only numeric codes and not alphanumeric.

sample data would help and in a workbook!

[NBVC]

either C4 is being entered as text (maybe cell preformatted as text).. or the entries in column A are text.

Try these... one should work:

=VLOOKUP(TEXT(C4,"@"),'Org Codes'!$A$2:$B$3000,2,FALSE)

or

=VLOOKUP(C4+0,'Org Codes'!$A$2:$B$3000,2,FALSE)

[busybeemg]

Thanks, this works for one but then not the other. Is there a way to combine your two plus mine, into one statement that says if this formula doesn't work then try this one, if not this one then this one, etc...

Thanks!!!!

either C4 is being entered as text (maybe cell preformatted as text).. or the entries in column A are text.

Try these... one should work:

=VLOOKUP(TEXT(C4,"@"),'Org Codes'!$A$2:$B$3000,2,FALSE)

or

=VLOOKUP(C4+0,'Org Codes'!$A$2:$B$3000,2,FALSE)

[NBVC]

Perhaps:

Please Login or Register  to view this content.

but better to perhaps set the cell and range to the same format from the start.

To convert to text only...

Select Column A2:A3000 and go to Data|Text to Columns, skip to 3rd window and select TEXT.

Repeat for Cell C4

and then use the original VLOOKUP(C4,... )