Forum Discussion

Microsoft

Aug 06, 2020

Solved

What is this invisible data format?

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 ma...

excel

SergeiBaklan

Aug 06, 2020

tonyr1129

Not 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

SergeiBaklan

Diamond Contributor

Aug 06, 2020

tonyr1129

Or don't care about formats and convert directly in formula adding double dash in front of lookup array reference.

tonyr1129

Microsoft

Aug 06, 2020

SergeiBaklan 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!

SergeiBaklan
Diamond Contributor
Aug 06, 2020
tonyr1129

Not 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
- tonyr1129
  Microsoft
  Aug 06, 2020
  SergeiBaklan These are really great examples. Thank you! The first suggestion that I edited also seems to work.
  - SergeiBaklan
    Diamond Contributor
    Aug 07, 2020
    tonyr1129 , you are welcome