Vlookup Problems

[OliviaGrey]

Hey All

Im using this formula to do a Vlookup =VLOOKUP(A:A,Sheet1!A:G,7,TRUE)

Any product codes that are not numbers show as #N/A but it works when the SKU is just a number.

I have tried formatting the cells as numbers. If its general it just shows 3E+11 everywhere

Any Ideas how I solve this please



[TABLE="width: 921"]
<tbody>[TR]
[TD="class: xl64, width: 181"]SKU[/TD]
[TD="width: 392"]Name[/TD]
[TD="width: 87"]In stock?[/TD]
[TD="width: 87"]Stock[/TD]
[TD="width: 174, colspan: 2"]Backorders allowed?[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD]2PC Chloe Set | Black | Demoniq Lingerie[/TD]
[TD]backorder[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"]DEMKOCHL001L[/TD]
[TD]2PC Chloe Set | Black | Demoniq Lingerie - Large, Black[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]DEMKOCHL001M[/TD]
[TD]2PC Chloe Set | Black | Demoniq Lingerie - Medium, Black[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]DEMKOCHL001S[/TD]
[TD]2PC Chloe Set | Black | Demoniq Lingerie - Small, Black[/TD]
[TD]backorder[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"][/TD]
[TD]2PC Chloe Set | White | Demoniq Lingerie[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]DEMKOCHLP001L[/TD]
[TD]2PC Chloe Set | White | Demoniq Lingerie - Large[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]DEMKOCHLP001M[/TD]
[TD]2PC Chloe Set | White | Demoniq Lingerie - Medium[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]DEMKOCHLP001S[/TD]
[TD]2PC Chloe Set | White | Demoniq Lingerie - Small[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64"]DEMKOCHLP001[/TD]
[TD]2PC Chloe Set | White | Demoniq Lingerie - XL[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]700000257026[/TD]
[TD]5 Piece Hogtie Set | Lux Fetish[/TD]
[TD]backorder[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD]In Stock[/TD]
[/TR]
</tbody>[/TABLE]

 

[Saba Sabaratnam]

Use false instead of true as the vlookup last argument

 

[OliviaGrey]

If I use False then none of the results come through. They are all #N/A

 

[Saba Sabaratnam]

My apology. I got it other way around.

Please use TRUE for approximate lookup (false is for exact lookup).

 

[Saba Sabaratnam]

Please ignore my previous post.

VLOOKUP(A:A,Sheet1!A:G,7,TRUE). it is not right.

Use only one cell reference in first argument (not A: A)which is a whole column). Use A3, A$ etc and with false.

 

[OliviaGrey]

Changed the cell to =VLOOKUP(A11,Sheet1!A:G,7,FALSE) I Get "#N/A"

If I use =VLOOKUP(A11,Sheet1!A:G,7,TRUE) I Get "In Stock" which is what I am looking for.

But this only works on SKU "700000257026" not on any of the others in this format "DEMKOCHL001L"