Home

Help with either VLOOKUP or IF AND statements

%3CLINGO-SUB%20id%3D%22lingo-sub-428637%22%20slang%3D%22en-US%22%3EHelp%20with%20either%20VLOOKUP%20or%20IF%20AND%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-428637%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3ENew%20here%2C%20hoping%20for%20help%20with%20quite%20a%20simple%20formula%20that%20I%20can't%20figure%20out..%3CBR%20%2F%3E%3CBR%20%2F%3EExample..%3CBR%20%2F%3EI%20have%20a%20drop%20down%20list%20with%20titles%20M1%2C%20M2%20and%20Both.%20I%20have%20the%20formula%20set%20so%20that%20if%20M2%20is%20selected%20in%20cell%20A1%2C%20the%20cell%20in%20B1%20gives%20value%20%22100%22..%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20it%20set%20so%20that%20if%20A1%20has%20%22M2%22%2C%20%22M1%22%20or%20%22Both%22%2C%20B1%20will%20then%20give%20values%20%22100%22%20%2280%22%20or%20%2290%22%20...%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20anyone%20help%20please%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-428637%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-429614%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20either%20VLOOKUP%20or%20IF%20AND%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-429614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F318575%22%20target%3D%22_blank%22%3E%40Benjwg%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DLOOKUP(A1%2C%20%7B%22Both%22%2C%22M1%22%2C%22M2%22%7D%2C%20%7B%2290%22%2C%2280%22%2C%22100%22%7D)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-429682%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20either%20VLOOKUP%20or%20IF%20AND%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-429682%22%20slang%3D%22en-US%22%3ETo%20make%20this%20as%20flexible%20as%20needed%2C%20you%20could%20enter%20M1%2C%20M2%20and%20Both%20into%20a%20small%20table%20with%20the%20approrpiate%20numbers%20next%20to%20them.%20Then%20you%20can%20use%20VLOOKUP%20to%20lookup%20the%20information%20from%20that%20table.%3C%2FLINGO-BODY%3E
Highlighted
Benjwg
Occasional Visitor
Hello,

New here, hoping for help with quite a simple formula that I can't figure out..

Example..
I have a drop down list with titles M1, M2 and Both. I have the formula set so that if M2 is selected in cell A1, the cell in B1 gives value "100"..

I need it set so that if A1 has "M2", "M1" or "Both", B1 will then give values "100" "80" or "90" ...

Can anyone help please?

Thanks.
2 Replies
Highlighted

@Benjwg , that could be

=LOOKUP(A1, {"Both","M1","M2"}, {"90","80","100"})
Highlighted
To make this as flexible as needed, you could enter M1, M2 and Both into a small table with the approrpiate numbers next to them. Then you can use VLOOKUP to lookup the information from that table.