Multiple VLOOKUP in an IF statement

[Richard Doyle]

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", i
it is then display "MAIN" if it is not then check column A o
worksheet"Lower", if it is there display "LOWER" if it is nowhere the
display "NONE" I had the following formula which works fine except fo
showing N/A instead of "NONE"

=IF(VLOOKUP(A1,Main!$A$1:$A$5,1,TRUE)=A1,"MAIN",(IF(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 i
very sporadic as to whether it works or not, some will return th
correct value and others will return the wrong value, i.e. it is presen
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

Richar

 

[GS]

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",(IF(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

 

[Claus Busch]

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