# Using IF IFNA and IFERROR in one formula

#### brooksc29

##### Active Member
I am trying to search by a name through three different ranges and am having trouble coming back with the correct result.

I'd like the formula to be written as lookup the name in cell B2, through range A, if there is no result, then look up the name in B2 in range B, and if no result look up the name in range C.

I have it written with SUMIFS formula, maybe that is giving me the problem? I am returning a FALSE result.

=ISNA(IFERROR(IF(VLOOKUP(\$B\$2,PACRIMARMS19,8,FALSE)&"-"&VLOOKUP(\$B\$2,PACRIMARMS19,9,FALSE),,SUMIFS(STATS19MiLpREG!\$E\$3:\$E\$45693,STATS19MiLpREG!\$B\$3:\$B\$45693,\$B\$2)&" - "&SUMIFS(STATS19MiLpREG!\$F\$3:\$F\$45693,STATS19MiLpREG!\$B\$3:\$B\$45693,\$B\$2)),VLOOKUP(\$B\$2,STATS19MLpERA,3,FALSE)&" - "&VLOOKUP(\$B\$2,STATS19MLpERA,4,FALSE)))

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### jasonb75

##### Well-known Member
Your formula makes absolutely no sense, why are you joining 2 vlookups together?

Normally you would use =IFERROR(VLOOKUP(...),IFERROR(VLOOKUP(...),IFERROR(VLOOKUP(...),"No Match")))

But I can't figure out from your formula if that is what you will need.

#### brooksc29

##### Active Member
I am joining VLOOKUP(\$B\$2,PACRIMARMS19,8,FALSE)&"-"&VLOOKUP(\$B\$2,PACRIMARMS19,9,FALSE) because of formatting the data correctly in the cell that I want it displayed. We are talking about two different columns of data being displayed into one cell.

PACRIMARMS19 is range A
STATS19MiLpREG!\$E\$3:\$E\$45693 is range B
STATS19MLpERA is range C

I'll try your way and see if it works.

#### jasonb75

##### Well-known Member
You've written your formula as IF(VLOOKUP(...)&"-"&VLOOKUP(...), but IF what? There is no logical test so that will always return a #VALUE! error.

The TRUE result of IF is empty, the FALSE result has the joined SUMIFS formula, but at the logical test is incomplete neither TRUE or FALSE will ever be used. The formula will always revert to the IFERROR result of the second pair of VLOOKUPs.

Regardless of the result of those VLOOKUPS, your formula will only ever return TRUE or FALSE because it is wrapped in ISNA, a mismatch on either of the VLOOKUPs in the second pair will return a result of TRUE because it will be #N/A! or if both have a valid result then it will return FALSE because a valid result is not #N/A!

Replies
4
Views
223
Replies
2
Views
117
Replies
7
Views
92
Replies
6
Views
64
Replies
10
Views
333