How to lookup while match has more than one value

**ForNeir45** · 28-11-2010

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?

**sergioKomic** · 28-11-2010

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!

**Blazej** · 28-11-2010

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.

**CitricAcid** · 28-11-2010

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.

**$tatic** · 28-11-2010

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"

**GeforceUser** · 29-11-2010

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.