Search and return value dilemma

%3CLINGO-SUB%20id%3D%22lingo-sub-1802797%22%20slang%3D%22en-US%22%3ESearch%20and%20return%20value%20dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1802797%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%3EI%20have%20a%20data%20table%20that%20looks%20something%20like%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pic1.jpg%22%20style%3D%22width%3A%20937px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228236i90825D4D9EF1C5DA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Pic1.jpg%22%20alt%3D%22Pic1.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMy%20return%20value%20datasheet%20looks%20something%20like%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pic2.jpg%22%20style%3D%22width%3A%20934px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228238iE63BC98D94698071%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Pic2.jpg%22%20alt%3D%22Pic2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20say%20that%20if%20Name%20and%20Month%20is%20matching%2C%20then%20return%20the%20value.%26nbsp%3B%20Otherwise%2C%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1802797%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1802861%22%20slang%3D%22de-DE%22%3ESubject%3A%20Search%20and%20return%20value%20dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1802861%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F835216%22%20target%3D%22_blank%22%3E%40Maddy1010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20picture%2C%20for%20example%2C%20you%20cannot%20see%20where%20the%20words%20%22Apple%2C%20Carrot%2C%20etc.%22%20takes.%3C%2FP%3E%3CP%3EIt%20is%20not%20clear%20what%20your%20plan%20is.%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%20Even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%20You%20could%20get%20a%20precise%20solution%20much%20faster%20with%20a%20file%20(w%2Fout%20sensitive%20data).%20This%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWish%20you%20a%20nice%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1802864%22%20slang%3D%22de-DE%22%3ERE%3A%20Search%20and%20return%20value%20dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1802864%22%20slang%3D%22de-DE%22%3EIt%20is%20also%20helpful%20to%20know%20the%20operating%20system%20and%20Excel%20version%2C%20as%20different%20approaches%20may%20be%20required%20depending%20on%20the%20version%20and%20OS.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1802987%22%20slang%3D%22en-US%22%3EBetreff%3A%20Search%20and%20return%20value%20dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1802987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20message.%26nbsp%3B%20Please%20see%20attached%20excel%20file.%26nbsp%3B%20I'm%20trying%20to%20retrieve%20data%20from%20'Source%20File'%20columns%20EZ%20to%20FK%20and%20return%20values%20in%20'Return%20Value'%20tab%2C%20columns%20labeled%20'Source%20File'.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1803151%22%20slang%3D%22en-US%22%3EBetreff%3A%20Search%20and%20return%20value%20dilemma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1803151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F835216%22%20target%3D%22_blank%22%3E%40Maddy1010%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20D3%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIF('Source%20File'!%24A%244%3A%24A%2449%2CLEFT('Return%20Value'!%24A3%2C5)%2COFFSET('Source%20File'!%24EZ%244%3A%24EZ%2449%2C0%2C(COLUMN()-4)%2F3))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20if%20your%20data%20extend%20below%20row%2049.%3C%2FP%3E%0A%3CP%3EFill%20down%20to%20D5.%3C%2FP%3E%0A%3CP%3ESelect%20D3%3AD5.%3C%2FP%3E%0A%3CP%3ECopy%20the%20range.%3C%2FP%3E%0A%3CP%3EPaste%20to%20G3%2C%20J3%2C%20M3%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

@Hans Vogelaar 

I have a data table that looks something like:

Pic1.jpg

My return value datasheet looks something like:

Pic2.jpg

I am trying to say that if Name and Month is matching, then return the value.  Otherwise, 0.

 

5 Replies
Highlighted

@Maddy1010 

In your picture, for example, you cannot see where the words "Apple, Carrot, etc." takes.

It is not clear what your plan is.

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. You could get a precise solution much faster with a file (w/out sensitive data). This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

 

Thank you for your understanding and patience

 

Wish you a nice day.

 

Nikolino

I know I don't know anything (Socrates)

Highlighted
It is also helpful to know the operating system and Excel version, as different approaches may be required depending on the version and OS.
Highlighted

@Nikolino Thanks for your message.  Please see attached excel file.  I'm trying to retrieve data from 'Source File' columns EZ to FK and return values in 'Return Value' tab, columns labeled 'Source File'.  Thank you!

Highlighted

@Maddy1010 

Enter the following formula in D3:

 

=SUMIF('Source File'!$A$4:$A$49,LEFT('Return Value'!$A3,5),OFFSET('Source File'!$EZ$4:$EZ$49,0,(COLUMN()-4)/3))

 

Adjust the ranges if your data extend below row 49.

Fill down to D5.

Select D3:D5.

Copy the range.

Paste to G3, J3, M3 etc.

Highlighted

@Hans Vogelaar Works AGAIN! Thank you - you are making my life easy.