#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default VLOOKUP()

Hi All,

I am using Excel 2007.

My Question is this :-

The below function checks whether the account number entered by the user in
E15 is valid. If it is it displays,it displays the customer name in F15.

How can i amend this function to show that if an account number is not valid
( ie. not found by the lookup function) in F15 the message is displayed thus
' Account number not valid,please try again ', instead as it does now F15
remains blank until a valid account number is entered in E15.

All assistance greatfully accepted.

=IF(ISNA(VLOOKUP(E15,customers!$A$1:$B$300,2,0)),"
",VLOOKUP(E15,customers!$A$1:$B$300,2,0))

FinanceGuru
--
Wales - the land of fire breathing dragons and rugby playing wizards.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default VLOOKUP()

=IF(E15="","",IF(ISNA(VLOOKUP(E15,customers!$A$1:$ B$300,2,0)),"Account number
not valid, please try again",VLOOKUP(E15,customers!$A$1:$B$300,2,0)))


"Finance Guru" wrote:

Hi All,

I am using Excel 2007.

My Question is this :-

The below function checks whether the account number entered by the user in
E15 is valid. If it is it displays,it displays the customer name in F15.

How can i amend this function to show that if an account number is not valid
( ie. not found by the lookup function) in F15 the message is displayed thus
' Account number not valid,please try again ', instead as it does now F15
remains blank until a valid account number is entered in E15.

All assistance greatfully accepted.

=IF(ISNA(VLOOKUP(E15,customers!$A$1:$B$300,2,0)),"
",VLOOKUP(E15,customers!$A$1:$B$300,2,0))

FinanceGuru
--
Wales - the land of fire breathing dragons and rugby playing wizards.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default VLOOKUP()

Many thanks Teethless mama. Much appreciated
--
Wales - the land of fire breathing dragons and rugby palaying wizards.


"Teethless mama" wrote:

=IF(E15="","",IF(ISNA(VLOOKUP(E15,customers!$A$1:$ B$300,2,0)),"Account number
not valid, please try again",VLOOKUP(E15,customers!$A$1:$B$300,2,0)))


"Finance Guru" wrote:

Hi All,

I am using Excel 2007.

My Question is this :-

The below function checks whether the account number entered by the user in
E15 is valid. If it is it displays,it displays the customer name in F15.

How can i amend this function to show that if an account number is not valid
( ie. not found by the lookup function) in F15 the message is displayed thus
' Account number not valid,please try again ', instead as it does now F15
remains blank until a valid account number is entered in E15.

All assistance greatfully accepted.

=IF(ISNA(VLOOKUP(E15,customers!$A$1:$B$300,2,0)),"
",VLOOKUP(E15,customers!$A$1:$B$300,2,0))

FinanceGuru
--
Wales - the land of fire breathing dragons and rugby playing wizards.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup in vlookup - taking the result as array name SupperDuck Excel Worksheet Functions 2 June 2nd 07 11:05 AM
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP buffgirl71 Excel Discussion (Misc queries) 12 November 15th 06 12:36 AM
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) MikeR-Oz New Users to Excel 1 March 22nd 06 10:01 AM
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( Oso Excel Worksheet Functions 2 January 26th 05 07:56 AM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 11:43 AM


All times are GMT +1. The time now is 11:21 AM.

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

About Us

"It's about Microsoft Excel"