Using IF IFNA and IFERROR in one formula

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
330
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)))
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jul 25, 2010
Messages
330
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
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,904
Messages
5,544,978
Members
410,647
Latest member
LegenDSlayeR
Top