#### snewsome

##### New Member
Hopefully I'm at the tail end of working with this formula, but I need help adjusting the below formula to return a blank cell instead of #N/A or "False" which is what the formula is currently doing.

=IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,TYPE!B:C,2,FALSE)))))

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
=IFERROR(IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,TYPE!B:C,2,FALSE))))),"") will eliminate the #n/a

=IFERROR(IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,TYPE!B:C,2,FALSE))))),"") will eliminate the #n/a

It's saying too many arguments...

It's saying too many arguments...

Nevermind...it removed the #n/a, but I'm seeing "FALSE" as well. Not sure how to adjust it to not return a FALSE?

The False you are seeing is part of your OR argument when all the arguments are False it will return a False. I think this will work but it is a bit large. someone I am sure has a shorter way to do it.

=IF(IFERROR(IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,type!B:C,2,FALSE))))),"")=FALSE,"",IFERROR(IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,type!B:C,2,FALSE))))),""))

The False you are seeing is part of your OR argument when all the arguments are False it will return a False. I think this will work but it is a bit large. someone I am sure has a shorter way to do it.

=IF(IFERROR(IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,type!B:C,2,FALSE))))),"")=FALSE,"",IFERROR(IF(AND(OR(M5={5,6,9}),(D5="SU")),LEFT(H5,7)&".59010",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5="M"),LEFT(H5,7)&".71020",IF(AND(OR(M5={1,2,3,4,5,6,9}),L5<>"M"),H5&(VLOOKUP(D5,type!B:C,2,FALSE))))),""))

OMG!! Yep that worked!! I didn't think it would be that convoluted. Thank you Thank you Thank you!!

thank you for the feedback.

Replies
4
Views
406
Replies
1
Views
265
Replies
7
Views
758
Replies
0
Views
255
Replies
6
Views
297

1,203,607
Messages
6,056,285
Members
444,855
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back