Lookup formula in another sheet and multiple other operations..

Hello!
My name is Riccardo. I'm asking your help to write an excel formula which should include many functions in one formula.. which is very tricky

Basically, I cannot post any screenshot for privacy of data but i will try to summarize it in words! My situation is kind of:

Sheet 1

------A----------B----------C
1 [Value1] [Name1] [Empty1]
2 [Value2] [Name2] [Empty2]
3 [Value3] [Name3] [Empty3]

Sheet 2

-------A--------B(Min)------C(MAX)............G
1 [Name1] [m.Value1] [M.Value1] ... [$Value1]
2 [Name1] [m.Value2] [M.Value2] ... [$Value2]
3 [Name2] [m.Value3] [M.Value3] ... [$Value3]


I need a function or a formula that can place in Sheet 1 [Empty1] a value from Sheet2

-> Go [Sheet 2] and match the [Name Sheet 1] with [Name Sheet 2]

-> If the name is the same then return on [Sheet 1] and find the associated [Value]

-> Take the [Value] from Sheet1 and look if [Value Sheet 1] is less then or equal to [M.Value1] and is greater than or equal to [m.Value1] in Sheet 2

-> If it's in the range m.Value1 =< Value1 >= M.Value1 then move XX cells in Sheet 2 from the [Name] and insert in [Empty1] the value of [$Value1]


I know! It's really tricky and I will understand if there will be no way to do that, I'm having a headache about it and thought only you guys could help me out.
I really recognize the complexity of the formula which I think includes several vlookups but it is not possible to do more than one.
I have searched a lot on the internet too but being my very specific need I can not find anything.
I hope you can help me with the formula or direct me to a guide that I can follow.

Many thanks in advance!!!

I understand that you cannot post the live file. However, for anyone to develop a practical solution, you DO NEED to produce an anonymised sample file with some typical values AND your expected results.

I'm sure it is doable BUT give us something to work with.

Thank you for replying TMS!

OK! I prepared an anonymised sample file!
So, there are SHEET 1 and SHEET 2

My goal would be filling the column [$$$ SHEET 1] with the values in [$$$ SHEET 2] but only after the check of the [NAME] and the confront of the [VALUE] near the name in sheet 1 with the range of [BEGIN CONCAT.] =< [VALUE (from sheet 1)] >= [END CONCAT.]. Then as above mentioned the formula should go to the associated [$$$ SHEET 2] value and insert it in the [$$$ SHEET 1] cell.

What a mess! sorry if I was not clear but its really tricky for me and I remain available for any clarifications!

Thank you so much!

See if this works for you:

Formula:

Please Login or Register  to view this content.

Sadly it doesn't work, but it does something!! Thank you for replying again TMS!

I will post a second file updated with your formula and some comments near reporting the value that should be there so that maybe it can guide you better.

Thank you!

Yes, I have been thinking about it. Using CONCAT gives you text values and you cannot do numeric comparisons of text values. Might need to change either the cells to real dates or try to change the formula to cope.

EDIT:

I added in extra columns to turn years and month into actual dates that are numbers that can be compared and IT WORKS!!!

TMS thank you so much!

I have changed the CONCAT formulae on both sheets to use DATE. NOTE the end date also use EOMONTH.

See if this works for you.

You're welcome. Thanks for the rep.

Beat me to it

Do you need extra columns? Or can you use the approach that I suggested?

Hi. You don't actually need the SUM. In the original attempt, I was returning more than one entry. So I chose to sum them. I also tried TRANSPOSING them. However, when we fixed the dates, it became redundant.

So:

=IFERROR(
FILTER('SHEET 2'!$W$2:$W$23, . . . Range to filter . . . the values you want to return
('SHEET 2'!$A$2:$A$23=D2)* . . . . . First condition . . . . . }
('SHEET 2'!$F$2:$F$23<=C2)* . . . . Second condition . . . } multiplied together
('SHEET 2'!$I$2:$I$23>=C2)), . . . . Third condition . . . . . }
"no entries") . . . . . . . . . . . . . . . . . Text/value to return if there is an error (typically, no matches: #CALC)