Formula Problem: Not this address but the other, and blank if no address

[RLCornish]

I'm putting together a workbook where I can load one sheet (raw data) and then in another sheet (product) extract the information I need for a specific format.

The thought process is if the 1st address contains "Box", as in PO Box, then pull back the 2nd address. The criteria I'm using to look up is a unique ID. However, if there's no criteria to look up then rather than N/A show the cell as blank.

I worked out the formula to look thru the 1st address and if "Box" is contained pull back the 2nd address. However, I can't figure out how to incorporate ISNA, or whatever formula is best here, to eliminate the N/A. Here's the working formula to get the right address...

=IF(ISNUMBER(SEARCH("BOX",VLOOKUP($A54,MASTER!$D$2:$H$12000,5,0))),VLOOKUP($A54,MASTER!$D$2:$T$12000,13,0),VLOOKUP($A54,MASTER!$D$2:$L$12000,5,0))

This formula brings back "556 HEATHER GROVE CT" (this is the 1st address), as it should. If there's no criteria, or unique ID, this brings back N/A.

I tried dropping ISNA right after the first IF. Here's what that looked like (messy)...

=IF(ISNA(IF(ISNUMBER(SEARCH("BOX",VLOOKUP($A55,MASTER!$D$2:$H$12000,5,0)))," ")),IF(ISNUMBER(SEARCH("BOX",VLOOKUP($A55,MASTER!$D$2:$H$12000,5,0))),VLOOKUP($A55,MASTER!$D$2:$T$12000,13,0),VLOOKUP($A55,MASTER!$D$2:$L$12000,5,0)))

This formula just gives me FALSE, which is true in as much as it's not N/A, but I want the address to come back, not the word FALSE

I'm stuck and don't know what the formula should look like to eliminate the N/A and replace it with a blank. I feel like it's something simple that I'm missing but I've been staring at this for far too long, and need some help.

RLCornish

 

[Redwolfx]

Welcome to the Forum

Try

=Iferror(IF(ISNUMBER(SEARCH("BOX",VLOOKUP($A54,MASTER!$D$2:$H$12000,5,0))),VLOOKUP($A54,MASTER!$D$2:$T$12000,13,0),VLOOKUP($A54,MASTER!$D$2:$L$12000,5,0)),"")

 

[RLCornish]

OMG, Red, that is perfect!!! As with many I'm sure, I'm completely self-taught so I haven't used the IFERROR formula before. But be sure, I'll be reading up on why that works, but now I am thrilled to have a solution. Thank you so much!!!

 

[Redwolfx]

Glad to Help.

Thanks for the feedback