SOLVED

Return a corresponding value if the date is found in dataset

%3CLINGO-SUB%20id%3D%22lingo-sub-1562357%22%20slang%3D%22en-US%22%3EReturn%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562357%22%20slang%3D%22en-US%22%3E%3CP%3EHey%2C%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20attachement%20picture%20shows%20my%20problem.%3C%2FP%3E%3CP%3EI%20want%20a%20function%20to%20column%20b%20that%20calculates%20the%20last%20days%20value%20and%20adds%20sales%20from%20h%20column%20if%20the%20corresponding%20date%2C%20in%20A%20column%2C%20is%20in%20the%20G%20column.%20So%20for%20example%20B4%20should%20get%20the%20value%20from%20B3%20and%20H4%20(15%E2%82%AC)%20and%20add%20them%20together.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20I%20cannot%20use%20VLOOKUP%20in%20this%20context%20as%20in%20the%20real%20example%2C%20the%20data%20is%20not%20structured%20for%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20the%20one%20who%20can%20help%20me.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1562357%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562425%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562425%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747738%22%20target%3D%22_blank%22%3E%40Justme123%3C%2FA%3E%26nbsp%3BPerhaps%20this%20is%20what%20you%20need%20in%20B2%20and%20copy%20it%20down.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DN(B1)%2BIFERROR(VLOOKUP(A2%2CG%3AH%2C2%2CFALSE)%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BThe%20attached%20file%20contains%20an%20example%20based%20on%20your%20picture.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562500%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562500%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20of%20all%2C%20thank%20you%20for%20your%20quick%20reply.%26nbsp%3B%20You're%20already%20a%20livesaver.%20Unfortunately%20I%20(think%20I)%20can't%20use%20VLOOKUP%20as%20the%20%22sum%22%20column%20is%20before%20the%20%22dates%22%20column.%26nbsp%3B%20I%20also%20have%20two%20more%20conditions%20I%20need%20to%20fulfill.%20I've%20attached%20a%20Picture%20for%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20conditions%20are%20these%3A%20%3CFONT%3EType%201%20should%20be%20A%20and%20type%202%20should%20be%201.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562547%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747738%22%20target%3D%22_blank%22%3E%40Justme123%3C%2FA%3E%26nbsp%3BNot%20sure%20I%20follow%20what%20you%20want%20with%20the%20types.%20VLOOKUP%20works%20just%20fine%20with%20the%20structure%20in%20your%20examples.%20If%20I'm%20mistaken%2C%20yu%20can%20use%20XLOOKUP%20in%20case%20you%20are%20a%20modern%20version%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562575%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562575%22%20slang%3D%22en-US%22%3E%3CP%3EOh%20sorry%20for%20being%20confusing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20types%20just%20means%20that%20those%20are%20additional%20conditions%20that%20need%20to%20be%20fulfilld%20to%20include%20the%20sales%20for%20that%20specific%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20Picture%2C%20which%20I've%20posted%20under%20my%20previous%20reply%2C%20I%20would%20love%20the%20formula%2C%20in%20B%20%22Sum%22%20column%2C%26nbsp%3B%20exclude%20the%20%2223%20%E2%82%AC%22%20sales%20from%2017.8.2020%20as%20its%20type%201%20and%20type%202%20are%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20post%20an%20example%20in%20this%20situation%3F%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20thank%20you%20very%20much.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562647%22%20slang%3D%22de-DE%22%3ESubject%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562647%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747738%22%20target%3D%22_blank%22%3E%40Justme123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI'm%20not%20sure%2C%20because%20the%20translation%20is%20not%20the%20best%2C%20nor%20my%20English%20either%2C%20send%20you%20a%20small%20solution.%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3EThe%20formula%20is%20in%20column%20B.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20%22Best%20Answer%22%20and%20as%20Like%20(click%20thumbs%20up)%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%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%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562726%22%20slang%3D%22en-US%22%3EBetreff%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562726%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%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%2C%20sorry%20this%20is%20not%20what%20I'm%20looking%20for.%20I%20think%20you%20made%20a%20mistake%20because%20in%20your%20file%2C%20there's%20only%20one%20cell%20that%20has%20a%20value.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EPlease%20reference%20to%20the%20other%20answers%2C%20to%20see%20what%20I%20want%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20though%20regardless!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562744%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747738%22%20target%3D%22_blank%22%3E%40Justme123%3C%2FA%3E%26nbsp%3BPerhaps%20like%20int%20he%20attached%20file%2C%20provided%20you%20are%20willing%20to%20accept%20the%20use%20of%20a%20few%20%22helper%22%20cells%20(shaded%20green).%20Just%20to%20make%20it%20more%20transparent.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566615%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%2C%20really%20sorry%20for%20late%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20provided%20solution%20doesnt%20work%20for%20my%20situation%20but%20that's%20because%20I%20wasnt%20clear%20enough%20on%20my%20situation.%20I'm%20sorry%20for%20that.%20In%20the%20%22Final%20example%22%20attachment%20you%20can%20see%20how%20my%20data%20is%20constructed.%20As%20you%20can%20see%20from%20here%2C%20the%20vlookup%20function%20wouldn't%20work%20because%20the%20date%20of%20expected%20arrival%20is%20not%20on%20the%20leftside%20of%20the%20data-table%20we%20would%20like%20to%20index%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20there's%20two%20conditions%20which%20first%20one%20(E-column)%20is%20that%20it%20needs%20to%20be%20type%201%20and%20the%20second%20condition%20(I-column)%20is%20that%20it%20needs%20to%20be%20type%20A.%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%20I%20would%20love%20to%20get%20a%20formula%20that%20tells%20me%20in%20the%20c-column%2C%20that%20how%20much%20stuff%20is%20in%20storage%20in%20each%20day.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20unfortunately%20don't%20have%20access%20to%20xlookup%20but%20I%20can%20use%20use%20of%20helper%20cells.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566767%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747738%22%20target%3D%22_blank%22%3E%40Justme123%3C%2FA%3E%26nbsp%3BRather%20than%20uploading%20a%20picture%2C%20why%20not%20upload%20your%20file%2C%20or%20at%20least%20the%20relevant%20portion%20of%20it.That%20save%20me%20having%20to%20recreate%20the%20schedule%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20Otherwise%2C%20consider%20restructuring%20the%20data%20so%20that%20you%20can%20use%20VLOOKUP.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1566815%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20corresponding%20value%20if%20the%20date%20is%20found%20in%20dataset%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1566815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20bothering%20but%20thank%20you%2C%20I%20got%20it%20now!%26nbsp%3B%3C%2FP%3E%3CP%3EI%20should%20have%20posted%20an%20example%20file%2C%20sorry%20for%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20great%20week!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hey, 

the attachement picture shows my problem.

I want a function to column b that calculates the last days value and adds sales from h column if the corresponding date, in A column, is in the G column. So for example B4 should get the value from B3 and H4 (15€) and add them together. 

 

Unfortunately I cannot use VLOOKUP in this context as in the real example, the data is not structured for that. 

 

Thank you so much for the one who can help me. 

10 Replies
Highlighted

@Justme123 Perhaps this is what you need in B2 and copy it down.

=N(B1)+IFERROR(VLOOKUP(A2,G:H,2,FALSE),0)

 The attached file contains an example based on your picture.

 

Highlighted
Best Response confirmed by Justme123 (Occasional Contributor)
Solution

@Riny_van_Eekelen 

 

First of all, thank you for your quick reply.  You're already a livesaver. Unfortunately I (think I) can't use VLOOKUP as the "sum" column is before the "dates" column.  I also have two more conditions I need to fulfill. I've attached a Picture for reference.

 

The conditions are these: Type 1 should be A and type 2 should be 1. 

 

 

Highlighted

@Justme123 Not sure I follow what you want with the types. VLOOKUP works just fine with the structure in your examples. If I'm mistaken, yu can use XLOOKUP in case you are a modern version of Excel.

 

 

Highlighted

Oh sorry for being confusing.

 

The types just means that those are additional conditions that need to be fulfilld to include the sales for that specific day.

 

In the Picture, which I've posted under my previous reply, I would love the formula, in B "Sum" column,  exclude the "23 €" sales from 17.8.2020 as its type 1 and type 2 are wrong.

 

Can you post an example in this situation? 

But thank you very much. 

Highlighted

@Justme123 

I'm not sure, because the translation is not the best, nor my English either, send you a small solution.
The formula is in column B.

 

 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

 

Highlighted

@Nikolino

 

Hey, sorry this is not what I'm looking for. I think you made a mistake because in your file, there's only one cell that has a value.


Please reference to the other answers, to see what I want to do.

 

Thank you though regardless! 

Highlighted

@Justme123 Perhaps like int he attached file, provided you are willing to accept the use of a few "helper" cells (shaded green). Just to make it more transparent.

Highlighted

@Riny_van_Eekelen 

 

Hey, really sorry for late reply. 

Your provided solution doesnt work for my situation but that's because I wasnt clear enough on my situation. I'm sorry for that. In the "Final example" attachment you can see how my data is constructed. As you can see from here, the vlookup function wouldn't work because the date of expected arrival is not on the leftside of the data-table we would like to index for.

 

Again there's two conditions which first one (E-column) is that it needs to be type 1 and the second condition (I-column) is that it needs to be type A. 

Again I would love to get a formula that tells me in the c-column, that how much stuff is in storage in each day. 

I unfortunately don't have access to xlookup but I can use use of helper cells. 

Highlighted

@Justme123 Rather than uploading a picture, why not upload your file, or at least the relevant portion of it.That save me having to recreate the schedule Otherwise, consider restructuring the data so that you can use VLOOKUP. 

 

 

Highlighted

@Riny_van_Eekelen 

 

Sorry for bothering but thank you, I got it now! 

I should have posted an example file, sorry for that.

 

Have a great week!