Using IF IFNA and IFERROR in one formula

[brooksc29]

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)))

 

[jasonb75]

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]

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]

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!