Search and return value dilemma'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%3C'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%3C'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
Occasional Contributor

@Hans Vogelaar 

I have a data table that looks something like:


My return value datasheet looks something like:


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


5 Replies


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.



I know I don't know anything (Socrates)

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

@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!


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.

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