A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help me (Vlookup)



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 11:28 AM posted to microsoft.public.excel.worksheet.functions
Amin
external usenet poster
 
Posts: 181
Default Help me (Vlookup)

hi my problem here is that i get this #NA thing anytime i select a product
code more than twice.

data is formatted as tables in the lookup sheet with 757 rows.
my other sheet is just 5 rows but i have used data validation liminting the
entry to the codes. but i have realised that a few minutes a go that the #NA
keep coming up but when i choose a different code it gives me the the product
name.

if choose
lCQ 1
LCQ 2
LCQ 3 this gets me #NA
but as soon as that is changed i get a name
any reason for that please.
  #2  
Old April 23rd, 2010, 11:34 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Help me (Vlookup)

I am not sure I understood your query correctly. If you want to handle #NA
then modify your formula as below...Replace the text with the current vlookup
formula

=IF(ISNA(yourvlookupformula),"",yourvlookupformula )


--
Jacob (MVP - Excel)


"Amin" wrote:

hi my problem here is that i get this #NA thing anytime i select a product
code more than twice.

data is formatted as tables in the lookup sheet with 757 rows.
my other sheet is just 5 rows but i have used data validation liminting the
entry to the codes. but i have realised that a few minutes a go that the #NA
keep coming up but when i choose a different code it gives me the the product
name.

if choose
lCQ 1
LCQ 2
LCQ 3 this gets me #NA
but as soon as that is changed i get a name
any reason for that please.

  #3  
Old April 23rd, 2010, 12:07 PM posted to microsoft.public.excel.worksheet.functions
Amin
external usenet poster
 
Posts: 181
Default Help me (Vlookup)

Hi there thank you so much for the reply but have a look if am doing
something wrong the formula is not accepted after i typed in this
=IF(ISNA(VLOOKUP(A2,'Inventory List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)

"Jacob Skaria" wrote:

I am not sure I understood your query correctly. If you want to handle #NA
then modify your formula as below...Replace the text with the current vlookup
formula

=IF(ISNA(yourvlookupformula),"",yourvlookupformula )


--
Jacob (MVP - Excel)


"Amin" wrote:

hi my problem here is that i get this #NA thing anytime i select a product
code more than twice.

data is formatted as tables in the lookup sheet with 757 rows.
my other sheet is just 5 rows but i have used data validation liminting the
entry to the codes. but i have realised that a few minutes a go that the #NA
keep coming up but when i choose a different code it gives me the the product
name.

if choose
lCQ 1
LCQ 2
LCQ 3 this gets me #NA
but as soon as that is changed i get a name
any reason for that please.

  #4  
Old April 23rd, 2010, 12:15 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Help me (Vlookup)

Try the below...

'Inventory List'!O739 is not a valid array. Please edit to suit....

=IF(ISNA(VLOOKUP(A2,'Inventory List'!A1:O739,2,0)),"",
VLOOKUP(A2,'Inventory List'!A1:O739,2,0))

--
Jacob (MVP - Excel)


"Amin" wrote:

Hi there thank you so much for the reply but have a look if am doing
something wrong the formula is not accepted after i typed in this
=IF(ISNA(VLOOKUP(A2,'Inventory List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)

"Jacob Skaria" wrote:

I am not sure I understood your query correctly. If you want to handle #NA
then modify your formula as below...Replace the text with the current vlookup
formula

=IF(ISNA(yourvlookupformula),"",yourvlookupformula )


--
Jacob (MVP - Excel)


"Amin" wrote:

hi my problem here is that i get this #NA thing anytime i select a product
code more than twice.

data is formatted as tables in the lookup sheet with 757 rows.
my other sheet is just 5 rows but i have used data validation liminting the
entry to the codes. but i have realised that a few minutes a go that the #NA
keep coming up but when i choose a different code it gives me the the product
name.

if choose
lCQ 1
LCQ 2
LCQ 3 this gets me #NA
but as soon as that is changed i get a name
any reason for that please.

  #5  
Old April 23rd, 2010, 12:48 PM posted to microsoft.public.excel.worksheet.functions
Amin
external usenet poster
 
Posts: 181
Default Help me (Vlookup)

Hey dude my eye brows have raised here, can you then kinindly in your own
time explain to me why this i have done below happens including yor own
formular. thank you.
FORMULAR AND RESULTS:
=VLOOKUP(A2,'Inventory List'!A1:O739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-FK French Kiss LCQ
LCQ-O Opulent LCQ
LCQ-T TRUE LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P #N/A
LCQ-P #N/A

FORMULAR AND RESULTS:
=VLOOKUP(A2,'Inventory List'!$A$1:$O$739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ


FORMULAR AND RESULTS:
=IF(ISNA(VLOOKUP(A2,'Inventory List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-FK French Kiss LCQ
LCQ-O Opulent LCQ
LCQ-T TRUE LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P
LCQ-P

Iwill be very grateful


"Jacob Skaria" wrote:

Try the below...

'Inventory List'!O739 is not a valid array. Please edit to suit....

=IF(ISNA(VLOOKUP(A2,'Inventory List'!A1:O739,2,0)),"",
VLOOKUP(A2,'Inventory List'!A1:O739,2,0))

--
Jacob (MVP - Excel)


"Amin" wrote:

Hi there thank you so much for the reply but have a look if am doing
something wrong the formula is not accepted after i typed in this
=IF(ISNA(VLOOKUP(A2,'Inventory List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)

"Jacob Skaria" wrote:

I am not sure I understood your query correctly. If you want to handle #NA
then modify your formula as below...Replace the text with the current vlookup
formula

=IF(ISNA(yourvlookupformula),"",yourvlookupformula )


--
Jacob (MVP - Excel)


"Amin" wrote:

hi my problem here is that i get this #NA thing anytime i select a product
code more than twice.

data is formatted as tables in the lookup sheet with 757 rows.
my other sheet is just 5 rows but i have used data validation liminting the
entry to the codes. but i have realised that a few minutes a go that the #NA
keep coming up but when i choose a different code it gives me the the product
name.

if choose
lCQ 1
LCQ 2
LCQ 3 this gets me #NA
but as soon as that is changed i get a name
any reason for that please.

  #6  
Old April 23rd, 2010, 01:44 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default Help me (Vlookup)

Check out the below by Debra on troubleshooting VLOOKUP()

http://contextures.com/xlFunctions02.html#Trouble

--
Jacob (MVP - Excel)


"Amin" wrote:

Hey dude my eye brows have raised here, can you then kinindly in your own
time explain to me why this i have done below happens including yor own
formular. thank you.
FORMULAR AND RESULTS:
=VLOOKUP(A2,'Inventory List'!A1:O739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-FK French Kiss LCQ
LCQ-O Opulent LCQ
LCQ-T TRUE LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P #N/A
LCQ-P #N/A

FORMULAR AND RESULTS:
=VLOOKUP(A2,'Inventory List'!$A$1:$O$739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ


FORMULAR AND RESULTS:
=IF(ISNA(VLOOKUP(A2,'Inventory List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-FK French Kiss LCQ
LCQ-O Opulent LCQ
LCQ-T TRUE LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P
LCQ-P

Iwill be very grateful


"Jacob Skaria" wrote:

Try the below...

'Inventory List'!O739 is not a valid array. Please edit to suit....

=IF(ISNA(VLOOKUP(A2,'Inventory List'!A1:O739,2,0)),"",
VLOOKUP(A2,'Inventory List'!A1:O739,2,0))

--
Jacob (MVP - Excel)


"Amin" wrote:

Hi there thank you so much for the reply but have a look if am doing
something wrong the formula is not accepted after i typed in this
=IF(ISNA(VLOOKUP(A2,'Inventory List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)

"Jacob Skaria" wrote:

I am not sure I understood your query correctly. If you want to handle #NA
then modify your formula as below...Replace the text with the current vlookup
formula

=IF(ISNA(yourvlookupformula),"",yourvlookupformula )


--
Jacob (MVP - Excel)


"Amin" wrote:

hi my problem here is that i get this #NA thing anytime i select a product
code more than twice.

data is formatted as tables in the lookup sheet with 757 rows.
my other sheet is just 5 rows but i have used data validation liminting the
entry to the codes. but i have realised that a few minutes a go that the #NA
keep coming up but when i choose a different code it gives me the the product
name.

if choose
lCQ 1
LCQ 2
LCQ 3 this gets me #NA
but as soon as that is changed i get a name
any reason for that please.

  #7  
Old April 23rd, 2010, 07:37 PM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default Help me (Vlookup)

Perhaps try this, looks like you are missing a ) after each ...FALSE) in the
formula. Notice the ...FALSE)) in the formula below.

=IF(ISNA(VLOOKUP(A2,'Inventory List'!O739,2,FALSE)),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE))

HTH
Regards,
Howard

"Amin" wrote in message
...
Hey dude my eye brows have raised here, can you then kinindly in your own
time explain to me why this i have done below happens including yor own
formular. thank you.
FORMULAR AND RESULTS:
=VLOOKUP(A2,'Inventory List'!A1:O739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-FK French Kiss LCQ
LCQ-O Opulent LCQ
LCQ-T TRUE LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P #N/A
LCQ-P #N/A

FORMULAR AND RESULTS:
=VLOOKUP(A2,'Inventory List'!$A$1:$O$739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ


FORMULAR AND RESULTS:
=IF(ISNA(VLOOKUP(A2,'Inventory
List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)
LCQ-D Dreamer LCQ
LCQ-CHIC Chic LCQ
LCQ-FK French Kiss LCQ
LCQ-O Opulent LCQ
LCQ-T TRUE LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P Plush LCQ
LCQ-P
LCQ-P

Iwill be very grateful


"Jacob Skaria" wrote:

Try the below...

'Inventory List'!O739 is not a valid array. Please edit to suit....

=IF(ISNA(VLOOKUP(A2,'Inventory List'!A1:O739,2,0)),"",
VLOOKUP(A2,'Inventory List'!A1:O739,2,0))

--
Jacob (MVP - Excel)


"Amin" wrote:

Hi there thank you so much for the reply but have a look if am doing
something wrong the formula is not accepted after i typed in this
=IF(ISNA(VLOOKUP(A2,'Inventory
List'!O739,2,FALSE),"",VLOOKUP(A2,'Inventory
List'!O739,2,FALSE)

"Jacob Skaria" wrote:

I am not sure I understood your query correctly. If you want to
handle #NA
then modify your formula as below...Replace the text with the current
vlookup
formula

=IF(ISNA(yourvlookupformula),"",yourvlookupformula )


--
Jacob (MVP - Excel)


"Amin" wrote:

hi my problem here is that i get this #NA thing anytime i select a
product
code more than twice.

data is formatted as tables in the lookup sheet with 757 rows.
my other sheet is just 5 rows but i have used data validation
liminting the
entry to the codes. but i have realised that a few minutes a go
that the #NA
keep coming up but when i choose a different code it gives me the
the product
name.

if choose
lCQ 1
LCQ 2
LCQ 3 this gets me #NA
but as soon as that is changed i get a name
any reason for that please.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.