| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I avoid #NA result in a VLOOKUP
I am trying to vlookup a number stored as text. I have verified and
reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
It would help if you showed us the formula and some sample table data
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "VKL Narayanan" wrote in message ... I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
Thanks
The formula is reproduced below =VLOOKUP(D80,$D$923:$F$1025,3,FALSE) D80 is a numbe formatted as text, the arry is given below Account Description Set 2011001 akfjdkljfl Loc+CC 2021001 akfjdkljfl Loc+CC D80 is searching for values under the column "Account" and I have a value 2011001 in D80 and it is still returning #NA, I formatted the column as text, deleted and pasted the values, but nothing is working. "Bernard Liengme" wrote: It would help if you showed us the formula and some sample table data best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "VKL Narayanan" wrote in message ... I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
Try
=VLOOKUP(--D80,$D$923:$F$1025,3,FALSE) -- __________________________________ HTH Bob "VKL Narayanan" wrote in message ... Thanks The formula is reproduced below =VLOOKUP(D80,$D$923:$F$1025,3,FALSE) D80 is a numbe formatted as text, the arry is given below Account Description Set 2011001 akfjdkljfl Loc+CC 2021001 akfjdkljfl Loc+CC D80 is searching for values under the column "Account" and I have a value 2011001 in D80 and it is still returning #NA, I formatted the column as text, deleted and pasted the values, but nothing is working. "Bernard Liengme" wrote: It would help if you showed us the formula and some sample table data best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "VKL Narayanan" wrote in message ... I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
ok, thanks, I tried and it worked,
what is the meaning of adding -- "Bob Phillips" wrote: Try =VLOOKUP(--D80,$D$923:$F$1025,3,FALSE) -- __________________________________ HTH Bob "VKL Narayanan" wrote in message ... Thanks The formula is reproduced below =VLOOKUP(D80,$D$923:$F$1025,3,FALSE) D80 is a numbe formatted as text, the arry is given below Account Description Set 2011001 akfjdkljfl Loc+CC 2021001 akfjdkljfl Loc+CC D80 is searching for values under the column "Account" and I have a value 2011001 in D80 and it is still returning #NA, I formatted the column as text, deleted and pasted the values, but nothing is working. "Bernard Liengme" wrote: It would help if you showed us the formula and some sample table data best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "VKL Narayanan" wrote in message ... I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
Hi Narayanan
-- converts boolean values to 1 and 0. Try the below formulas =--TRUE returns 1 =--FALSE returns 0 Take a look at JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html If this post helps click Yes --------------- Jacob Skaria "VKL Narayanan" wrote: ok, thanks, I tried and it worked, what is the meaning of adding -- "Bob Phillips" wrote: Try =VLOOKUP(--D80,$D$923:$F$1025,3,FALSE) -- __________________________________ HTH Bob "VKL Narayanan" wrote in message ... Thanks The formula is reproduced below =VLOOKUP(D80,$D$923:$F$1025,3,FALSE) D80 is a numbe formatted as text, the arry is given below Account Description Set 2011001 akfjdkljfl Loc+CC 2021001 akfjdkljfl Loc+CC D80 is searching for values under the column "Account" and I have a value 2011001 in D80 and it is still returning #NA, I formatted the column as text, deleted and pasted the values, but nothing is working. "Bernard Liengme" wrote: It would help if you showed us the formula and some sample table data best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "VKL Narayanan" wrote in message ... I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
Oops; I have pasted the wrong link; It should have been
http://mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Narayanan -- converts boolean values to 1 and 0. Try the below formulas =--TRUE returns 1 =--FALSE returns 0 Take a look at JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html If this post helps click Yes --------------- Jacob Skaria "VKL Narayanan" wrote: ok, thanks, I tried and it worked, what is the meaning of adding -- "Bob Phillips" wrote: Try =VLOOKUP(--D80,$D$923:$F$1025,3,FALSE) -- __________________________________ HTH Bob "VKL Narayanan" wrote in message ... Thanks The formula is reproduced below =VLOOKUP(D80,$D$923:$F$1025,3,FALSE) D80 is a numbe formatted as text, the arry is given below Account Description Set 2011001 akfjdkljfl Loc+CC 2021001 akfjdkljfl Loc+CC D80 is searching for values under the column "Account" and I have a value 2011001 in D80 and it is still returning #NA, I formatted the column as text, deleted and pasted the values, but nothing is working. "Bernard Liengme" wrote: It would help if you showed us the formula and some sample table data best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "VKL Narayanan" wrote in message ... I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
Hi,
The lookup value AND the lookup column in the lookup table must all be of the same data type. Either both text or both numbers. You can use the VALUE function to convert text numbers to number but the -- trick is favored by most of the experts here. Although I wouldn't do it you could convert the table range, within the formula to text instead of converting the lookup number to a value. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "VKL Narayanan" wrote: I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I avoid #NA result in a VLOOKUP
It actually converts the text value to its numeric equivalent here.
-- __________________________________ HTH Bob "Jacob Skaria" wrote in message ... Hi Narayanan -- converts boolean values to 1 and 0. Try the below formulas =--TRUE returns 1 =--FALSE returns 0 Take a look at JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html If this post helps click Yes --------------- Jacob Skaria "VKL Narayanan" wrote: ok, thanks, I tried and it worked, what is the meaning of adding -- "Bob Phillips" wrote: Try =VLOOKUP(--D80,$D$923:$F$1025,3,FALSE) -- __________________________________ HTH Bob "VKL Narayanan" wrote in message ... Thanks The formula is reproduced below =VLOOKUP(D80,$D$923:$F$1025,3,FALSE) D80 is a numbe formatted as text, the arry is given below Account Description Set 2011001 akfjdkljfl Loc+CC 2021001 akfjdkljfl Loc+CC D80 is searching for values under the column "Account" and I have a value 2011001 in D80 and it is still returning #NA, I formatted the column as text, deleted and pasted the values, but nothing is working. "Bernard Liengme" wrote: It would help if you showed us the formula and some sample table data best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "VKL Narayanan" wrote in message ... I am trying to vlookup a number stored as text. I have verified and reverified the formula nothing seems to be wrong, the syntex is correct and the field references are correct, but it keeps returning for all cells #NA. How to debug this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| vlookup shows result one cell above the expected result | Excel Worksheet Functions | |||
| VLOOKUP - Can't see result | Excel Worksheet Functions | |||
| vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
| How to avoid the #Value! result in a formula? | Excel Worksheet Functions | |||
![VKL Narayanan[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
