Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to lookup while match has more than one value

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 28-11-2010
Member
 
Join Date: Nov 2010
Posts: 38
How to lookup while match has more than one value
  

Followings are my formula:
Code:
=IF(ISERROR(VLOOKUP($E12,'psah customer database'!$B$2:$C$15902,2,FALSE)),1,VLOOKUP($E12,'psah customer database'!$B$2:$C$15902,2,FALSE))
At the present, I have items in the range 'psah customer database'!$B$2:$C$15902 that might or might not have copied. How can I get it to return a few sort of notification which there is additional than one matching substance in the list and not just return the value of the primary one on the list?

Reply With Quote
  #2  
Old 28-11-2010
Member
 
Join Date: May 2008
Posts: 913
Re: How to lookup while match has more than one value

Substitute counta with COUNTIF:
Code:
=IF(COUNTIF('psah customer database'!$B$2:$B$15902,$E11)>1,"Multiple Matches”, IF(COUNTIF('psah customer database'!$B$2:$B$15902,$E11)=0,0,VLOOKUP($E11,'psah customer database'!$B$2:$C$15902,2,FALSE)))
I would make use of named ranges in this application to build the formula a bit shorter and easier to read. “psah" customer database, that sheet name is completely extremely extended!
Reply With Quote
  #3  
Old 28-11-2010
Member
 
Join Date: May 2008
Posts: 860
Re: How to lookup while match has more than one value

IsError is a slight hazardous to make use of in that formula as it will take no notice of all errors not only the NA produced if a corresponding value could not be establish. At this point is my favored solution.
Code:
=IF(counta('psah customer database'!$B$2:$B$15902,$E12)>1, "Multiple Matches", if(counta('psah customer database'!$B$2:$B$15902,$E12)=0,0,VLOOKUP($E12,'psah customer database'!$B$2:$C$15902,2,FALSE)))
This formula calculates the figure of matching entries and adapts the reply consequently. Counta does a text match that means that if you attempt to discover a number in a group of text or vice versa it will return #NA letting you be familiar with that you have a data type problem.
Reply With Quote
  #4  
Old 28-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,022
Re: How to lookup while match has more than one value

First off all in Excel 2007 your method can be abridged to:
Code:
=IFERROR(VLOOKUP($E12,'psah customer database'!$B$2:$C$15902,2,FALSE),"")
At the moment attempt this array formula for returning manifold matches. Enter as an array and heave down until is begin producing blanks. It is a lengthy formula so paste into the formula bar to make sure it does not tear into two or additional cells.
Code:
=IFERROR(INDEX('psah customer database'!$C$1:$C$15902,LARGE(('psah customer database'!$B$1:$B$15902=$E$12)*ROW($B$1:$B$15902),COUNTIF('psah customer database'!$B$1:$B$15902,$E$11)+1-ROW(A1))),"")
This is array formulas that have to be entered by pressing CTRL+Shift+Enter and not only enter. If you do it appropriately then Excel will place curly brackets around the formula {}. You cannot enter this physically. If you edit the formula you must have to enter it another time with CTRL+Shift+Enter.
Reply With Quote
  #5  
Old 28-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,221
Re: How to lookup while match has more than one value

Attempt this formula:
Code:
=IF(ISNA(VLOOKUP($E12,'psah customer database'!$B$2:$C$15902,2,FALSE)),0,IF(COUNTIF('psah customer database'!$B$2:$C$15902,$E12)>1,"Multiple Matches",VLOOKUP($E12,'psah customer database'!$B$2:$C$15902,2,FALSE)))
No match = 0
1 match = the value in Column C
Additional than 1 match = text message "Multiple Matches"
Reply With Quote
  #6  
Old 29-11-2010
Member
 
Join Date: Mar 2009
Posts: 1,360
Re: How to lookup while match has more than one value

I am satisfied in excel but actually just opening in complex formulas. Starting with the attached formula, in the "paycost" tab which is the detail required, there are manifold values which could be returned. It is returning the primary value of manifold lines. I have attempted the over formulas and cannot obtain it to work. Is there a method to insert the formula to return multiple matches with a coma unraveling them? Thank you for any information you can make available, with any luck I have specified you sufficient aspect of what I wish for.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to lookup while match has more than one value"
Thread Thread Starter Forum Replies Last Post
DNS Lookup failure ANUP Windows Server Help 3 15-09-2009 11:17 PM
DNS lookup time out HELOISE Networking & Security 4 25-08-2009 12:10 PM
What is PageRank Lookup? robin45 Technology & Internet 2 25-08-2009 04:20 AM
Reverse lookup a SID Kool Active Directory 1 20-07-2009 10:53 PM


All times are GMT +5.5. The time now is 03:42 AM.