Recommended Solution for using IF, IFS, or Lookup in Excel Data Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2137215%22%20slang%3D%22en-US%22%3ERecommended%20Solution%20for%20using%20IF%2C%20IFS%2C%20or%20Lookup%20in%20Excel%20Data%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2137215%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys!%3C%2FP%3E%3CP%3EI'm%20working%20on%20my%20data%20table%20and%20used%20this%20formula%20to%20come%20up%20with%20random%20orders%20for%20my%20customers%20to%20save%20me%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCHOOSE(RANDBETWEEN(1%2C11)%2C%22Conchas%22%2C%22Champurrado%22%2C%20%22Puerquitos%22%2C%20%22Ponche%22%2C%20%22Empanadas%22%2C%20%22Atole%22%2C%20%22Conos%20de%20Crema%22%2C%20%22Caf%C3%A9%20de%20Olla%22%2C%20%22Besos%22%2C%22Chocolate%20Caliente%22%2C%20%22Mantecadas%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20now%20I'm%20working%20on%20a%20category%20column%2C%20to%20where%20it%20should%20show%20me%20a%20food%20item%20as%20a%20pastry%20but%20if%20they're%20a%20non-food%20item%20a%20drink.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20using%20an%20IF%20and%20IFS%20function%20but%20it%20kept%20showing%20%23NAME%3F%20instead%20of%20the%20result%20or%20that%20it%20was%20too%20few%20arguments.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20Conchas%2C%20Puerquitos%2C%20Empanadas%2C%20Conos%20de%20Crema%2C%20Besos%2C%20and%20Mantecadas%20shown%20as%20a%20%22Pastry%22%20result%20and%20Champurrado%2C%20Ponche%2C%20Atole%2C%26nbsp%3BCaf%C3%A9%20de%20Olla%2C%20and%20Chocolate%20Caliente%20shown%20as%20a%20%22Drink%22%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20y'all%20suggest%20the%20format%20of%20my%20formula%20to%20be%3F%20And%20what%20should%20I%20use%3F%20I%20would%20really%20appreciate%20y'alls%20help!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2137215%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2137251%22%20slang%3D%22en-US%22%3ERe%3A%20Recommended%20Solution%20for%20using%20IF%2C%20IFS%2C%20or%20Lookup%20in%20Excel%20Data%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2137251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F967461%22%20target%3D%22_blank%22%3E%40whalien52%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20create%20a%20list%20of%20items%20and%20categories%2C%20for%20example%20on%20another%20sheet%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0121.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255066i170E0C6BD2B656F6%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0121.png%22%20alt%3D%22S0121.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20a%20VLOOKUP%20formula.%20In%20the%20example%20below%2C%20I%20assume%20that%20the%20sheet%20with%20the%20list%20is%20Sheet2.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0122.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F255067i2B65C85FB4358817%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0122.png%22%20alt%3D%22S0122.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20B2%20is%20%3DVLOOKUP(A2%2CSheet2!%24A%242%3A%24B%2412%2C2%2CFALSE)%3C%2FP%3E%0A%3CP%3EPS%20in%20A2%2C%20I%20used%20%3DINDEX(Sheet2!%24A%242%3A%24A%2412%2CRANDBETWEEN(1%2C11))%3C%2FP%3E%0A%3CP%3EThis%20makes%20it%20easy%20to%20change%20an%20item%20in%20the%20list.%20Both%20formulas%20will%20pick%20up%20the%20change%20automatically.%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi guys!

I'm working on my data table and used this formula to come up with random orders for my customers to save me time.

 

=CHOOSE(RANDBETWEEN(1,11),"Conchas","Champurrado", "Puerquitos", "Ponche", "Empanadas", "Atole", "Conos de Crema", "Café de Olla", "Besos","Chocolate Caliente", "Mantecadas")

 

However, now I'm working on a category column, to where it should show me a food item as a pastry but if they're a non-food item a drink.  

 

This is what I used: 

 

=IF((OR(D3=Conchas,D3=Puerquitos,D3=Empanadas,D3=Conos de Crema,D3=Besos,D3=Mantecadas)),"Pastry","Drink")

 

but it kept showing #NAME? instead of the result 

 

I would like to have Conchas, Puerquitos, Empanadas, Conos de Crema, Besos, and Mantecadas shown as a "Pastry" result and Champurrado, Ponche, Atole, Café de Olla, and Chocolate Caliente shown as a "Drink" result.

 

How would y'all suggest the format of my formula to be? And what should I use? I would really appreciate y'alls help!  

1 Reply

@whalien52 

I'd create a list of items and categories, for example on another sheet:

S0121.png

You can then use a VLOOKUP formula. In the example below, I assume that the sheet with the list is Sheet2.

S0122.png

The formula in B2 is =VLOOKUP(A2,Sheet2!$A$2:$B$12,2,FALSE)

PS in A2, I used =INDEX(Sheet2!$A$2:$A$12,RANDBETWEEN(1,11))

This makes it easy to change an item in the list. Both formulas will pick up the change automatically.