Aug 06 2020 02:04 PM
Hello, I have received a data file where columns C,D,E (see image 1) have numbers in them. The data format shows as "General". However, when I do a lookup (vlookup/xlookup), it can't find a single match on the C column for example. My formula was correct. Then I double-clicked on a cell of col C, and after Enter, it immediately right-aligned itself (see image 2, where you can see the first 2 cells are right-aligned after double-clicking on each, or simply Enter when it's selected in the formula bar)! The lookup functions work perfectly on those. So, what's hiding in those cells? And how can I a) Identify this problem 2) and quickly reformat them instead of manually double-clicking on each cell? (I've tried Clear Formatting, changing to numbers, accounting, used CLEAN() but nothing worked to fix it other manually double-clicking). I've seen this before, but any help on how to identify and resolve it efficiently is greatly appreciated. This is for a Microsoft project. Thank you.
image 1 (original):
image 2 (after double-clickli
Aug 06 2020 02:40 PM
This often happens when data are imported from another source (for example a web page) into an Excel worksheet. Although the data look like numbers, Excel sees them as text.
A simple way to convert them to 'real' numbers in one go:
Aug 06 2020 03:00 PM
Or don't care about formats and convert directly in formula adding double dash in front of lookup array reference.
Aug 06 2020 03:05 PM - edited Aug 06 2020 04:09 PM
@Sergei Baklan Thank you for your suggestion. So that I understand correctly, let's say my formula is: =VLOOKUP([@[Current PartnerONE ID]],FY20MPLoffboard!$A$2:$C$510,3,FALSE)
(and let's also assume FY20MPLoffboard is the sheet that contains this trouble-some columns)
Is your suggestion then to rewrite the formula as below?
=VLOOKUP([@[Current PartnerONE ID]],--FY20MPLoffboard!$A$2:$C$510,3,FALSE)
Can you please confirm? That'd be a real time-saver if double-dash can work through this type of issues!
Aug 06 2020 03:06 PM
@Hans Vogelaar Thank you for your suggestion! I thought about it, now I know that's actually used by others as a workaround too.
Aug 06 2020 03:23 PM
SolutionNot exactly, that's like
=VLOOKUP([@[Current PartnerONE ID]],--FY20MPLoffboard!$A$2:$C$510,3,FALSE)
alternatively
=VLOOKUP([@[Current PartnerONE ID]],VALUE(FY20MPLoffboard!$A$2:$C$510),3,FALSE)
or
=VLOOKUP([@[Current PartnerONE ID]],1*FY20MPLoffboard!$A$2:$C$510,3,FALSE)
or like
Aug 06 2020 05:10 PM
@Sergei Baklan These are really great examples. Thank you! The first suggestion that I edited also seems to work.
Aug 06 2020 03:23 PM
SolutionNot exactly, that's like
=VLOOKUP([@[Current PartnerONE ID]],--FY20MPLoffboard!$A$2:$C$510,3,FALSE)
alternatively
=VLOOKUP([@[Current PartnerONE ID]],VALUE(FY20MPLoffboard!$A$2:$C$510),3,FALSE)
or
=VLOOKUP([@[Current PartnerONE ID]],1*FY20MPLoffboard!$A$2:$C$510,3,FALSE)
or like