SOLVED

help with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3337793%22%20slang%3D%22en-US%22%3Ehelp%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3337793%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20sorry%20if%20this%20is%20simple%20but%20I%20just%20cannot%20figure%20it%20out.%20I%20have%20a%20drop%20down%20data%20column%20with%20three%20sizes%2C%204x6%2C%205x7%2C%20and%209x4%20in%20column%20A.%20in%20column%20D%20my%20heading%20is%20%22cards%20per%20sheet%22.%20If%20I%20select%20%224x6%22%20it%20should%20say%2220%22%20in%20column%20D.%20If%20%225x7%22%20-%20%2216%22%2C%20and%20if%20%229x4%22%20-%20%2215%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20figure%20out%20how%20to%20use%20an%20IF%20statement%20to%20return%20those%20values.%20What%20can%20I%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3337793%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-3337860%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3337860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383839%22%20target%3D%22_blank%22%3E%40JohnShaw19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20drop-downs%20are%20in%20A2%20and%20below.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20D2%2C%20then%20fill%20down%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(A2%3D%224x6%22%2C20%2CIF(A2%3D%225x7%22%2C16%2CIF(A2%3D%229x4%22%2C15%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3337873%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3337873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383839%22%20target%3D%22_blank%22%3E%40JohnShaw19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20use%20a%20lookup%20table.%20The%20first%20column%20can%20be%20the%20source%20for%20the%20data%20validation%20drop-down.%3C%2FP%3E%0A%3CP%3EIn%20the%20screenshot%20below%2C%20the%20table%20is%20named%20Sizes.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1413.png%22%20style%3D%22width%3A%20539px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369954i66B8A7A55E677052%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1413.png%22%20alt%3D%22S1413.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20B2%20(on%20your%20worksheet%20it%20would%20be%20D2)%20is%3C%2FP%3E%0A%3CP%3E%3DIFERROR(VLOOKUP(A2%2CSizes%2C2%2CFALSE)%2C%22%22)%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Microsoft%20365%20or%20Office%202021%2C%20you%20can%20also%20use%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(A2%2CSizes%5BSize%5D%2CSizes%5BCards%20per%20Sheet%5D%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3338348%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3338348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that%20info.%20So%2C%20in%20the%20first%20example%20I%20always%20get%20an%20error%20that%20says%20there%20was%20a%20typo%20in%20the%20formula%2C%20and%20it%20tries%20to%20correct%20it.%20But%20then%20I%20get%20an%20error%20that%20says%2C%20%22The%20value%20doesn't%20match%20the%20data%20validation%20restrictions%2C%20defined%20by%20this%20cell.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Column%20A2%20I%20have%20a%20data%20validation%20criterion%20of%2C%20%22list%22%20and%20in%20the%20Source%20I%20have%204x6%2C%205x7%2C%20and%209x4.%20That%20creates%20the%20drop-down%20values%20in%20column%20A2.%20This%20is%20how%20the%20%22form%22%20looks%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-05-07%20163802.jpg%22%20style%3D%22width%3A%20687px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369963i637DE9F6AF27291F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-05-07%20163802.jpg%22%20alt%3D%22Screenshot%202022-05-07%20163802.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3338438%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3338438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383839%22%20target%3D%22_blank%22%3E%40JohnShaw19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20sample%20workbook%20(without%20sensitive%20data)%2C%20or%20if%20that%20is%20not%20possible%2C%20make%20it%20available%20through%20OneDrive%2C%20Google%20Drive%2C%20Dropbox%20or%20similar%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3338459%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3338459%22%20slang%3D%22en-US%22%3Esure!%20There's%20no%20sensitive%20data.%20Just%20working%20out%20the%20basic%20spreadsheet.%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!Ag5CIttUj0lyiolPe-WH_GyAh0ZDcg%3Fe%3D76Ftg0%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!Ag5CIttUj0lyiolPe-WH_GyAh0ZDcg%3Fe%3D76Ftg0%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3338629%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3338629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1383839%22%20target%3D%22_blank%22%3E%40JohnShaw19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20forgot%202%20closing%20parentheses%20-%20sorry%20about%20that.%20The%20first%20formula%20should%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(A2%3D%224x6%22%2C20%2CIF(A2%3D%225x7%22%2C16%2CIF(A2%3D%229x4%22%2C15%2C%22%22)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20you%20have%20set%20up%20date%20validation%20for%20D2%20that%20only%20allows%20the%20values%201%2C%202%2C%20...%2C%2010.%20So%2020%2C%2016%20and%2015%20will%20be%20rejected.%3C%2FP%3E%0A%3CP%3EI'd%20remove%20the%20data%20validation%20from%20D2.%20If%20you%20use%20a%20formula%2C%20data%20validation%20is%20not%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3338647%22%20slang%3D%22en-US%22%3ERe%3A%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3338647%22%20slang%3D%22en-US%22%3EThat%20was%20it!!!%20Thanks%20so%20much!%20I%20honestly%20do%20not%20remember%20putting%20data%20values%20in%20D2.%20So%20much%20appreciate%20your%20help.%3C%2FLINGO-BODY%3E
New Contributor

I am sorry if this is simple but I just cannot figure it out. I have a drop down data column with three sizes, 4x6, 5x7, and 9x4 in column A. in column D my heading is "cards per sheet". If I select "4x6" it should say"20" in column D. If "5x7" - "16", and if "9x4" - "15".

 

I cannot figure out how to use an IF statement to return those values. What can I do?

7 Replies

@JohnShaw19 

Let's say the drop-downs are in A2 and below.

Enter the following formula in D2, then fill down:

 

=IF(A2="4x6",20,IF(A2="5x7",16,IF(A2="9x4",15,"")

@JohnShaw19 

Or use a lookup table. The first column can be the source for the data validation drop-down.

In the screenshot below, the table is named Sizes.

S1413.png

The formula in B2 (on your worksheet it would be D2) is

=IFERROR(VLOOKUP(A2,Sizes,2,FALSE),"")

If you have Microsoft 365 or Office 2021, you can also use

=XLOOKUP(A2,Sizes[Size],Sizes[Cards per Sheet],"")

@Hans Vogelaar 

 

Thanks for that info. So, in the first example I always get an error that says there was a typo in the formula, and it tries to correct it. But then I get an error that says, "The value doesn't match the data validation restrictions, defined by this cell."

 

In Column A2 I have a data validation criterion of, "list" and in the Source I have 4x6, 5x7, and 9x4. That creates the drop-down values in column A2. This is how the "form" looks:

 

Screenshot 2022-05-07 163802.jpg

@JohnShaw19 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

sure! There's no sensitive data. Just working out the basic spreadsheet.
https://1drv.ms/x/s!Ag5CIttUj0lyiolPe-WH_GyAh0ZDcg?e=76Ftg0
best response confirmed by JohnShaw19 (New Contributor)
Solution

@JohnShaw19 

I forgot 2 closing parentheses - sorry about that. The first formula should be

 

=IF(A2="4x6",20,IF(A2="5x7",16,IF(A2="9x4",15,"")))

 

But you have set up date validation for D2 that only allows the values 1, 2, ..., 10. So 20, 16 and 15 will be rejected.

I'd remove the data validation from D2. If you use a formula, data validation is not needed.

That was it!!! Thanks so much! I honestly do not remember putting data values in D2. So much appreciate your help.