

 Thread Tools  Search this Thread 
#1
 
 
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)) 
#2
 
 
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))) 
#3
 
 
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))) 
#4
 
 
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),"") 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)+1ROW(A1))),"") 
#5
 
 
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))) 1 match = the value in Column C Additional than 1 match = text message "Multiple Matches" 
#6
 
 
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. 

Tags: counta, countif, excel 2007, formula, ms exel 
Thread Tools  Search this Thread 

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  15092009 11:17 PM 
DNS lookup time out  HELOISE  Networking & Security  4  25082009 12:10 PM 
What is PageRank Lookup?  robin45  Technology & Internet  2  25082009 04:20 AM 
Reverse lookup a SID  Kool  Active Directory  1  20072009 10:53 PM 