Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default Multiple VLOOKUP in an IF statement

Hi I want to check if a value is present across multiple worksheets

So I have a list of integers in the cover sheet
1
2
3
4
5

I then want to look to see if "1" is in column A of worksheet"Main", if it is then display "MAIN" if it is not then check column A of worksheet"Lower", if it is there display "LOWER" if it is nowhere then display "NONE" I had the following formula which works fine except for showing N/A instead of "NONE"

=IF(VLOOKUP(A1,Main!$A$1:$A$5,1,TRUE)=A1,"MAIN",(I F(VLOOKUP(A1,Lower!$A$1:$A$5,1,FALSE)=A1,"LOWER"," NONE")))

however if I replace the numbers above for a string such as names it is very sporadic as to whether it works or not, some will return the correct value and others will return the wrong value, i.e. it is present in one of the worksheets yet still returns an N/A value

Any thoughts as to why it works with an integer but not a string?

Thanks

Richard
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default Multiple VLOOKUP in an IF statement

Richard Doyle was thinking very hard :
Hi I want to check if a value is present across multiple worksheets

So I have a list of integers in the cover sheet
1
2
3
4
5

I then want to look to see if "1" is in column A of worksheet"Main", if
it is then display "MAIN" if it is not then check column A of
worksheet"Lower", if it is there display "LOWER" if it is nowhere then
display "NONE" I had the following formula which works fine except for
showing N/A instead of "NONE"

=IF(VLOOKUP(A1,Main!$A$1:$A$5,1,TRUE)=A1,"MAIN",(I F(VLOOKUP(A1,Lower!$A$1:$A$5,1,FALSE)=A1,"LOWER"," NONE")))

however if I replace the numbers above for a string such as names it is
very sporadic as to whether it works or not, some will return the
correct value and others will return the wrong value, i.e. it is present
in one of the worksheets yet still returns an N/A value

Any thoughts as to why it works with an integer but not a string?

Thanks

Richard


What you`re getting is VLOOKUP`s standard return when it can`t find a
match. What happens is that your formula never gets to the False part
because the error occurs ahead of that. Look at using the ISERROR()
function inside your formula, perhaps wrapped with OR() function.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Multiple VLOOKUP in an IF statement

Hi Richard,

Am Fri, 16 Nov 2012 16:21:42 +0000 schrieb Richard Doyle:

1
2
3
4
5

I then want to look to see if "1" is in column A of worksheet"Main", if
it is then display "MAIN" if it is not then check column A of
worksheet"Lower", if it is there display "LOWER" if it is nowhere then
display "NONE" I had the following formula which works fine except for
showing N/A instead of "NONE"


you don't have to use VLOOKUP.
Try:
=IF(COUNTIF(Main!$A$1:$A$5,A1)0,"MAIN",IF(COUNTIF (Lower!$A$1:$A$5,A1)0,"LOWER","NONE"))



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
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, if statement, maybe an and statement Jennifer Excel Discussion (Misc queries) 1 February 26th 10 04:30 AM
If statement with vlookup link to multiple sheets Rechie Excel Discussion (Misc queries) 4 August 26th 09 12:26 PM
If statement with multiple criteria and multiple results Tickfarmer Excel Discussion (Misc queries) 3 January 28th 09 09:11 PM
Multiple if statement with vlookup function TamIam Excel Worksheet Functions 1 March 18th 08 07:01 PM
Using an IF statement, or VLOOKUP statement Tim Williams Excel Programming 0 December 8th 06 10:41 PM


All times are GMT +1. The time now is 01:09 PM.

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"