VLookup Errors
I am doing a VLookup from one sheet to another matching up rep codes -
everything is formatted as General. Our rep codes are either 2 numbers or 2 letters. The letters are working great but rep codes that are numbers return as N/A. Any idea how to fix that without changing the formatting? |
Erika,
I'm guessing that vookup isn't sorted numerically and therefore you'll need to use FALSE as the 3rd argument of Vlookup eg. =VLOOKUP(A1,A1:B100,2,FALSE) HTH Rob "Erika" wrote in message ... I am doing a VLookup from one sheet to another matching up rep codes - everything is formatted as General. Our rep codes are either 2 numbers or 2 letters. The letters are working great but rep codes that are numbers return as N/A. Any idea how to fix that without changing the formatting? |
Rob,
Here is my formula looks like =VLOOKUP(A5,Dailyfile!$A$1:$D$323,4,FALSE) Is that correct? "Rob" wrote: Erika, I'm guessing that vookup isn't sorted numerically and therefore you'll need to use FALSE as the 3rd argument of Vlookup eg. =VLOOKUP(A1,A1:B100,2,FALSE) HTH Rob "Erika" wrote in message ... I am doing a VLookup from one sheet to another matching up rep codes - everything is formatted as General. Our rep codes are either 2 numbers or 2 letters. The letters are working great but rep codes that are numbers return as N/A. Any idea how to fix that without changing the formatting? |
Erika,
That formula looks correct and should work regardless of whether number of text. If it isn't working, are the numbers in the range to lookup actually numbers i.e. could they have been imported an are in fact text but look like a number? Rob "Erika" wrote in message ... Rob, Here is my formula looks like =VLOOKUP(A5,Dailyfile!$A$1:$D$323,4,FALSE) Is that correct? "Rob" wrote: Erika, I'm guessing that vookup isn't sorted numerically and therefore you'll need to use FALSE as the 3rd argument of Vlookup eg. =VLOOKUP(A1,A1:B100,2,FALSE) HTH Rob "Erika" wrote in message ... I am doing a VLookup from one sheet to another matching up rep codes - everything is formatted as General. Our rep codes are either 2 numbers or 2 letters. The letters are working great but rep codes that are numbers return as N/A. Any idea how to fix that without changing the formatting? |
Formula looks correct. However the problem may be that the rep codes are
text in either the source or the lookup table. If both are not identical (either text or numbers) then you will get an error. To convert all of the numbers entered as text to numbers Place 1 in a blank cell Select that cell and select Edit/Copy Select the range of cells you need to convert and select Edit/Paste Special and select the "Multiply" option HTH PC "Erika" wrote in message ... Rob, Here is my formula looks like =VLOOKUP(A5,Dailyfile!$A$1:$D$323,4,FALSE) Is that correct? "Rob" wrote: Erika, I'm guessing that vookup isn't sorted numerically and therefore you'll need to use FALSE as the 3rd argument of Vlookup eg. =VLOOKUP(A1,A1:B100,2,FALSE) HTH Rob "Erika" wrote in message ... I am doing a VLookup from one sheet to another matching up rep codes - everything is formatted as General. Our rep codes are either 2 numbers or 2 letters. The letters are working great but rep codes that are numbers return as N/A. Any idea how to fix that without changing the formatting? |
All times are GMT +1. The time now is 10:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com