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 » New Users
Reload this Page Help please,what is wrong with this formula?
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help please,what is wrong with this formula?


« Previous Thread | Next Thread »

 
 
Thread Tools Display Modes
  #1  
Old December 9th, 2005, 10:35 AM posted to microsoft.public.excel.newusers
Mare
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?

Hi, help me with this please

=IF(B10="","",(VLOOKUP(B10,((ArtA!A2:C55000)(ArtB! A2:C49424)),3,FALSE)))

this should return value from 3rd column. It works with only one
sheet(ArtA), but when i try to include 2nd sheet (ArtB) it returns
'#REF!' error.
Question should probably be:" How to include 2nd sheet in VLOOKUP
function? "

Thanks


--
Mare
------------------------------------------------------------------------
Mare's Profile: http://www.excelforum.com/member.php...o&userid=27644
View this thread: http://www.excelforum.com/showthread...hreadid=492126
  #2  
Old December 9th, 2005, 10:48 AM posted to microsoft.public.excel.newusers
Jan Karel Pieterse
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?
Hi Mare,

Question should probably be:" How to include 2nd sheet in VLOOKUP
function? "

You can't. You can combine two VLOOKUP functions like this though:

=IF(ISERROR(VLOOKUP(B10,SheetA...)),VLOOKUP(B10,Sh eetB...),VLOOKUP(B10,
SheetA...))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
  #3  
Old December 9th, 2005, 11:26 AM posted to microsoft.public.excel.newusers
Roger Govier
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?
Hi

Jan Karel has posted you a solution to look in ArtB if the data is not found
in ArtA.
I interpreted your request slightly differently and maybe incorrectly) as
you wanting to add both values to the cell where the formula is placed.
If so then
=IF(B10="","",VLOOKUP(B10,ArtA!A2:C55000,3,FALSE)+
VLOOKUP(B10,ArtA!A2:C55000,3,FALSE))

Regards

Roger Govier


Mare wrote:
Hi, help me with this please

=IF(B10="","",(VLOOKUP(B10,((ArtA!A2:C55000)(ArtB! A2:C49424)),3,FALSE)))

this should return value from 3rd column. It works with only one
sheet(ArtA), but when i try to include 2nd sheet (ArtB) it returns
'#REF!' error.
Question should probably be:" How to include 2nd sheet in VLOOKUP
function? "

Thanks
  #4  
Old December 9th, 2005, 02:27 PM posted to microsoft.public.excel.newusers
Mare
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?

Hi Jan, Roger

thank You both for promt replyies.

Jan's solution should work for me, because I need VLOOKUP to look in
sheetB if it does not find data in sheetA, but... I can't make it to
work this is what I made of it:
=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C5500 0,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))


This only returns data from "ArtA" and returns '#n/a' when quired data
is in "ArtB".


It is probably obvious that I have started to use Excel today.


--
Mare
------------------------------------------------------------------------
Mare's Profile: http://www.excelforum.com/member.php...o&userid=27644
View this thread: http://www.excelforum.com/showthread...hreadid=492126
  #5  
Old December 9th, 2005, 02:40 PM posted to microsoft.public.excel.newusers
Jan Karel Pieterse
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?
Hi Mare,

=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C5500 0,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))

Make that:

=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000,3,FALSE)),V LOOKUP(B10,ArtA!A2:C55000,3,FALSE),VLOOKUP(B10,Ar
tB!A2:C49424,3,FALSE))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
  #6  
Old December 9th, 2005, 06:34 PM posted to microsoft.public.excel.newusers
Roger Govier
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?
Hi Mare

Then Jan gave you the correct solution.
You need to use his formula in the style he posted.
Try
=IF(B10="","",IF(ISERROR(VLOOKUP(B10,ArtA!A2:C5500 0,3 FALSE)),
VLOOKUP(B10,ArtB!A2:C55000,3,FALSE),VLOOKUP(B10,Ar tB!A2:C49424,3,FALSE)))


Regards

Roger Govier


Mare wrote:
Hi Jan, Roger

thank You both for promt replyies.

Jan's solution should work for me, because I need VLOOKUP to look in
sheetB if it does not find data in sheetA, but... I can't make it to
work this is what I made of it:
=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C5500 0,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))


This only returns data from "ArtA" and returns '#n/a' when quired data
is in "ArtB".


It is probably obvious that I have started to use Excel today.
  #7  
Old December 9th, 2005, 06:36 PM posted to microsoft.public.excel.newusers
Roger Govier
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?
My apologies Jan,

I had not seen you had already posted the answer to Mare until just after I
had hit the send button.

Regards

Roger Govier


Jan Karel Pieterse wrote:
Hi Mare,


=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000
ArtB!A2:C49424,3,FALSE)),VLOOKUP(B10,ArtA!A2:C55 000,3,FALSE),VLOOKUP(B10,ArtB!A2:C49424,3,FALSE))


Make that:

=IF(ISERROR(VLOOKUP(B10,ArtA!A2:C55000,3,FALSE)),V LOOKUP(B10,ArtA!A2:C55000,3,FALSE),VLOOKUP(B10,Ar
tB!A2:C49424,3,FALSE))

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
  #8  
Old December 9th, 2005, 07:16 PM posted to microsoft.public.excel.newusers
Jan Karel Pieterse
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?
Hi Roger,

I had not seen you had already posted the answer to Mare until just after I
had hit the send button.

No problem!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
  #9  
Old December 13th, 2005, 11:12 AM posted to microsoft.public.excel.newusers
Mare
external usenet poster
  
Posts: n/a
Default Help please,what is wrong with this formula?

Thank You Jan,
Thank You Roger,


Problem solved


--
Mare
------------------------------------------------------------------------
Mare's Profile: http://www.excelforum.com/member.php...o&userid=27644
View this thread: http://www.excelforum.com/showthread...hreadid=492126
 



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Worksheet Functions 10 December 3rd, 2005 05:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Worksheet Functions 17 November 25th, 2005 05:18 PM
What is wrong with this formula? grinlrar Worksheet Functions 4 October 1st, 2005 10:21 AM
Wrong answer after using the payment formula in excel punkyh New Users 8 April 28th, 2005 07:56 PM
Whats wrong with my formula? Bob Phillips Worksheet Functions 8 January 24th, 2004 12:31 AM


All times are GMT +1. The time now is 12:58 AM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

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