Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 20th 11, 09:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 153
Default Match and Vlookup issue

Hi, my workbook has 3 worksheets. On the first 2 worksheets are lists
of study numbers with various outstanding work (there will never be
matching study numbers on these 2 sheets, it's one or the other). The
first sheet is titled 'Current', the 2nd sheet is titled 'Waiting'.
The 3rd sheet (called 'ATFs') is an additional list of study numbers
that get's brought in from a separate report. I'm trying to use the
Match or Vlookup formula to find study numbers on the ATFs sheet that
match study numbers on one of the other 2 sheets. The formula that I
have tried is:

=IF(OR(MATCH($A3,'Current'!$A$2:$A$100,0)0,MATCH( $A3,'Waiting'!$A$2:$A$100,0)0),"Match","")

The thing is with this, if I try the formual with the Match only
looking at one sheet at a time, it seemily works - if there is a
match, then I get the row number where the match occurs, but if
there's not a match, I still get #NA.

I've tried a variation of this with the Vlookup formula:

=IF(ISNA(OR(VLOOKUP($A25,'Current'!$A$2:$A$100,1,F ALSE),VLOOKUP($A25,'Waiting'!$A$2:$A$100,1,FALSE)) ),"No","Match")

This returns the word "Match" if there is indeed a match on the
'Current' sheet, but otherwise returns 'No', even if there is a match
on the Waiting worksheet.

I'm confused. Any help/advice would be greatly appreciated. Thanks.

Frank

  #2   Report Post  
Old June 20th 11, 11:05 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default Match and Vlookup issue

On Jun 20, 1:10*pm, Phrank wrote:
=IF(OR(MATCH($A3,'Current'!$A$2:$A$100,0)0,
MATCH($A3,'Waiting'!$A$2:$A$100,0)0),"Match","")


That does not work because if either MATCH expression fails and
returns the #N/A error, OR() will return an error.

Try:

=IF(ISNUMBER(MATCH($A3,'Current'!$A$2:$A$100,0))," Match",
IF(ISNUMBER(MATCH($A3,'Waiting'!$A$2:$A$100,0)),"M atch",""))

This is also more efficient because IF() will evaluate the second
MATCH only if the first ones fails.
  #3   Report Post  
Old June 21st 11, 01:40 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 153
Default Match and Vlookup issue

Bingo! My mind got stuck in having to use the OR statement. It works
perfectly. Thank you!

Frank

On Mon, 20 Jun 2011 15:05:18 -0700 (PDT), joeu2004
wrote:

On Jun 20, 1:10*pm, Phrank wrote:
=IF(OR(MATCH($A3,'Current'!$A$2:$A$100,0)0,
MATCH($A3,'Waiting'!$A$2:$A$100,0)0),"Match","")


That does not work because if either MATCH expression fails and
returns the #N/A error, OR() will return an error.

Try:

=IF(ISNUMBER(MATCH($A3,'Current'!$A$2:$A$100,0)), "Match",
IF(ISNUMBER(MATCH($A3,'Waiting'!$A$2:$A$100,0))," Match",""))

This is also more efficient because IF() will evaluate the second
MATCH only if the first ones fails.



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 with Match - no results issue JICDB Excel Worksheet Functions 2 May 1st 09 02:13 PM
MATCH & INDEX ISSUE? [email protected] Excel Discussion (Misc queries) 1 March 15th 08 03:13 AM
VLOOKUP/Index&Match data format issue [email protected] Excel Worksheet Functions 3 April 4th 07 07:31 PM
Match Exact Issue Milrok Excel Worksheet Functions 1 February 13th 07 12:00 AM


All times are GMT +1. The time now is 12:18 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017