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.![Confused :confused: :confused:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
RLCornish
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