Home

Using VLOOKUP for SKUs stored as text

%3CLINGO-SUB%20id%3D%22lingo-sub-728685%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20for%20SKUs%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368423%22%20target%3D%22_blank%22%3E%40Brandon_1%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20provide%20us%20with%20a%20sample%20of%20the%20data%20you%20working%20on%20(including%20the%20VLOOKUP%20formula)%20as%20an%20attached%20spreadsheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728750%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20for%20SKUs%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728750%22%20slang%3D%22en-US%22%3EPerhaps%2C%20you%20can%20use%200%20as%20your%20SKU%2C%20custom%20format%20it%20as%200000%2C%20then%20use%200%20as%20your%20lookup_value%20argument%20in%20VLOOKUP.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728645%22%20slang%3D%22en-US%22%3EUsing%20VLOOKUP%20for%20SKUs%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20there%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20using%20vlookup%20and%20it's%20returning%20%23n%2Fa.%20I%20used%20%3Dtype()%20and%20found%20it's%20type%202%20which%20is%20text%2C%20which%20is%20why%20it%20isn't%20working.%20However%2C%20when%20I%20convert%20text%20to%20number%20I%20go%20from%20%220000%22%20to%20just%200%2C%20which%2C%20as%20a%20SKU%2C%20I%20can't%20allow.%20How%20do%20I%20keep%20%220000%22%20as%20text%20but%20still%20use%20vlookup%3F%20Thank%20you!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHere's%20an%20example%20data%20set%3A%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-728645%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728825%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20for%20SKUs%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728825%22%20slang%3D%22en-US%22%3E%3CP%3EI%20added%20an%20example%20above%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729042%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20for%20SKUs%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368423%22%20target%3D%22_blank%22%3E%40Brandon_1%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%2C%20you%20have%20two%20options%3A%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20recommended%20one%3A%20is%20to%20unify%20the%20datatype%20of%20the%20SKU%20in%20both%20tables.%3C%2FLI%3E%3CLI%3EThe%20other%20is%20to%20use%20INDEX%20%26amp%3B%20MATCH%20instead%20of%20VLOOKUP%20as%20follows%3A%3C%2FLI%3E%3C%2FUL%3E%3CPRE%3E%3DINDEX('Example%20Base%20Data'!%24B%242%3A%24B%245%2CINDEX(MATCH(--A2%2C--'Example%20Base%20Data'!%24A%242%3A%24A%245%2C0)%2C))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121627i3EFE3CC32494A11A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22INDEX%20%26amp%3B%20MATCH.png%22%20title%3D%22INDEX%20%26amp%3B%20MATCH.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20notice%20the%20double%20negative%26nbsp%3Bsign%20(--)%20used%20in%20MATCH%20function%2C%20it's%20used%20to%20convert%20the%20numbers%20that%20are%20stored%20as%20texts%20to%20numeric%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-729054%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20for%20SKUs%20stored%20as%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3BHi%2C%20it%20seems%20to%20work%20in%20that%20sheet.%20I%20can't%20get%20it%20working%20in%20my%20data%20but%20I'll%20keep%20messing%20around%20with%20it%20and%20let%20you%20know%20what%20I%20find.%20I%20appreciate%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Brandon_1
New Contributor

Hi there,

 

I'm using vlookup and it's returning #n/a. I used =type() and found it's type 2 which is text, which is why it isn't working. However, when I convert text to number I go from "0000" to just 0, which, as a SKU, I can't allow. How do I keep "0000" as text but still use vlookup? Thank you!

 

Here's an example data set:

5 Replies

@Brandon_1

 

Hi,

 

Could you provide us with a sample of the data you working on (including the VLOOKUP formula) as an attached spreadsheet?

Perhaps, you can use 0 as your SKU, custom format it as 0000, then use 0 as your lookup_value argument in VLOOKUP.

I added an example above @Haytham Amairah 

@Brandon_1

 

In this case, you have two options:

  • The recommended one: is to unify the datatype of the SKU in both tables.
  • The other is to use INDEX & MATCH instead of VLOOKUP as follows:
=INDEX('Example Base Data'!$B$2:$B$5,INDEX(MATCH(--A2,--'Example Base Data'!$A$2:$A$5,0),))

INDEX & MATCH.png

 

You will notice the double negative sign (--) used in MATCH function, it's used to convert the numbers that are stored as texts to numeric numbers.

 

Hope that helps

@Haytham Amairah Hi, it seems to work in that sheet. I can't get it working in my data but I'll keep messing around with it and let you know what I find. I appreciate the help!

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies