Iferror + if + or + vlookup

[Nanogirl21]

I am trying to combine 4 excel formulas. Can someone please help. Thank you.

On sheet called Tracking in Cell AF2 I’d like to create a formula with these rules…

(STEP 1) IF X2 contains the word Apple OR Oranges return VLOOKUP(B:B,Math!B:Z,18,FALSE).
(STEP 2) IF STEP 1 returns value of 0, (blank), -, or #N/A error return value located in AA2.

 

[Joe4]

Step 1 (formula in cell AF2):

=IF(OR(ISNUMBER(SEARCH("Apple",X2)),ISNUMBER(SEARCH("Oranges",X2))),VLOOKUP(B:B,Math!B:Z,18,FALSE),"")

Step 2:

=IF(ISNA(AF2),AA2,IF(OR(AF2=0,AF2="",AF2="-"),AA2,""))

 

[Nanogirl21]

Step 1 (formula in cell AF2):

=IF(OR(ISNUMBER(SEARCH("Apple",X2)),ISNUMBER(SEARCH("Oranges",X2))),VLOOKUP(B:B,Math!B:Z,18,FALSE),"")

Step 2:

=IF(ISNA(AF2),AA2,IF(OR(AF2=0,AF2="",AF2="-"),AA2,""))

Thank you. Is there any way to combine these and have 1 long formula? I'd prefer not to use another column for STEP 2.

 

[Joe4]

I don't think that is a good idea. If it were me and I were tasked to do it as one single formula, I would go the VBA route and create my own User Defined Function to do it.

The concept behind combining this too is not hard. You can try it, if yo like. Simply substitute every reference of AF2 in the second formula with the first formula. You can see how unwieldy that can get in a hurry (as you will be doing 4 substitutions). It may really make performance lag. And personally, I would cringe at any formula that has 4 VLOOKUPS in it.

Another option. Why not just simply hide the column with the Step1 formula in it?
It does not need to be visible for the second formula to reference it.

 

[Nanogirl21]

I don't think that is a good idea. If it were me and I were tasked to do it as one single formula, I would go the VBA route and create my own User Defined Function to do it.

The concept behind combining this too is not hard. You can try it, if yo like. Simply substitute every reference of AF2 in the second formula with the first formula. You can see how unwieldy that can get in a hurry (as you will be doing 4 substitutions). It may really make performance lag. And personally, I would cringe at any formula that has 4 VLOOKUPS in it.

Another option. Why not just simply hide the column with the Step1 formula in it?
It does not need to be visible for the second formula to reference it.

How would you create a user defined function? Sorry, i'm not too advanced with excel.

Thank you.

 

[Joe4]

How would you create a user defined function?

See here: https://support.office.com/en-us/ar...in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f