# combining two formulas into one

#### Lenna

##### Active Member

Formula 1
IF L2 is blank and J2 doesn’t contain (AT1R/LCTI/LPRI/LPRI_RPT/LPRII/LPRII_RPT), if true return “NoOrderNumberFound”, otherwise “Not Reported”.

Formula 2
IF L2 is not blank, lookup N2 in row P(has sorted unique values) and return either “WrongTest” if not found, or “CorrectTest” if it was found.

=IF(ISERROR(VLOOKUP(N2,P:P,1,FALSE)),"WrongTest","CorrectTest")

Thank you,

Lenna

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### skywriter

##### Well-known Member
You could use the choose function:

Code:
``=CHOOSE(IF(L2="",1,2),"Formula if L2 = Blank","Formula if L2 is not blank")``

#### Lenna

##### Active Member
What is 1,2 ?

You could use the choose function:

Code:
``=CHOOSE(IF(L2="",1,2),"Formula if L2 = Blank","Formula if L2 is not blank")``

#### Lenna

##### Active Member
I'm working on Formula 1. How do I get it to return either “NoOrderNumberFound” if it doesn't contain and “Not Reported” if it does?
This formula returns only True/False.

=OR(ISNUMBER(SEARCH("AT1R",\$L5)),ISNUMBER(SEARCH("LCTI",\$L5)), ISNUMBER(SEARCH("LPRI",\$L5)), ISNUMBER(SEARCH("LPRI_RPT",\$L5)), ISNUMBER(SEARCH("LPRII",\$L5)), ISNUMBER(SEARCH("LPRII_RPT",\$L5)))=FALSE

Thanks

#### skywriter

##### Well-known Member
What is 1,2 ?

It's the choose function, you have an index number, the 1 means it chooses the first formula I put in there, the 2 means it chooses the second.
If L2="", the result is one and it picks formula 1, otherwise the if evaluates to 2 and it chooses the the second formula.

I could say if something, have it evaluate to 1 if true and then have another if statement in the false and if that if statement evalutes to true it would equal 2 and then another if statement if false and have the next if statement evaluate to 3 etc. You seem to want to do a lot, so I thought choose would be a good option.

Read Excel help for the choose function for a better understanding.

#### skywriter

##### Well-known Member
I'm working on Formula 1. How do I get it to return either “NoOrderNumberFound” if it doesn't contain and “Not Reported” if it does?
This formula returns only True/False.

=OR(ISNUMBER(SEARCH("AT1R",\$L5)),ISNUMBER(SEARCH("LCTI",\$L5)), ISNUMBER(SEARCH("LPRI",\$L5)), ISNUMBER(SEARCH("LPRI_RPT",\$L5)), ISNUMBER(SEARCH("LPRII",\$L5)), ISNUMBER(SEARCH("LPRII_RPT",\$L5)))=FALSE

Thanks

OR only returns true of false. Put the OR statement in an If statement and then put what you want if it evaluates to true and whatever else you want if it evalutes to false.

#### Lenna

##### Active Member
This formula seems to work with both AND/OR. What is the difference?

=CHOOSE(IF(\$L5="",1,2), IF(AND(\$J5<>"AT1R",\$J5<>"LCTI",\$J5<>"LPRI",\$J5<>"LPRI_RPT",\$J5<>"LPRII",\$J5<>"LPRII_RPT"),"NoOrderNumberFound","Test Not Reported"),IF(ISERROR(VLOOKUP(\$J5,O:O,1,FALSE)),"WrongTestOrdered","CorrectTest"))

#### Lenna

##### Active Member
It needs to be an "AND".

Replies
3
Views
139
Replies
2
Views
134
Replies
2
Views
388
Replies
7
Views
426
Replies
8
Views
142

### Forum statistics

1,190,794
Messages
5,982,956
Members
439,808
Latest member
agutosay ### 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