LinkBack URL
About LinkBacks
The attached file explains the challenge well.
It is a multiple lookup search offseting columns to the right and to the left based on 2 provided values.
Hope can get your appreciated help.
Complicated row and column lookup
The attached file explains the challenge well.
It is a multiple lookup search offseting columns to the right and to the left based on 2 provided values.
Hope can get your appreciated help.
Last edited by 6StringJazzer; 05-23-2023 at 09:36 AM. Reason: Changed title from "I am trying to solve a finance question"
Are you still using Excel 2016?
It is Office 2019!
Last edited by danibitter75; 05-23-2023 at 09:55 AM. Reason: Wrong answer
Sorry...it is Microsoft Office Professional Plus 2019 now.
Try,
PHP Code:=INDEX(INDEX($M$3:$AF$24,,MATCH($D$2,$M$1:$AF$1,0)),MATCH($D$4-MIN(FILTER($D$4-INDEX($M$3:$AF$24,,MATCH($D$2,$M$1:$AF$1,0)+2),$D$4-INDEX($M$3:$AF$24,,MATCH($D$2,$M$1:$AF$1,0)+2)>0)),INDEX($M$3:$AF$24,,MATCH($D$2,$M$1:$AF$1,0)+2),0))
For the members having XLOOKUPFormula:
Please Login or Register to view this content.
Is XLOOKUP available in Excel 2019?
@danibitter75
Please update your profile to reflect the current version of Excel. Folks here post solutions based upon the version you are using.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Unfortunetely not available!
Ok..will do!
I have not a function called "FILTER"
Hi Dani,
I have a solution that takes 2 helper columns and a calculated min() cell. See the attached:
ExcelForumfromDaniel.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I have not the function "FILTER".Originally Posted by josephteh
Worked really fine! Reputation added! Many thanks for your efforts and God bless you!
D10=INDEX(INDEX(M$3:AF$24,,MATCH(D2&B10,$M$1:$AF$1&$M$2:$AF$2,0)),MATCH(AGGREGATE(14,6,INDEX(M$3:AF$24,,MATCH($D$2&$B$4,$M$1:$AF$1&$M$2:$AF$2,0))/(INDEX($M$3:$AF$24,,MATCH($D$2&$B$4,$M$1:$AF$1&$M$2:$AF$2,0))<=$D$4),1),INDEX($M$3:$AF$24,,MATCH($D$2&$B$4,$M$1:$AF$1&$M$2:$AF$2,0)),0))
control+shift+enter
Change your file like this
Dear Caracalla, you have manipulated the original file. In my real life situation I cannot do that.
Can you sort the data differently? The challenge of finding the next-smallest number can be greatly simplified if you do an ascending sort of the data in the "Of. Compra" columns.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Cannot modify the original file as it is linked to other infos and these infos are dynamic changing using RTD external link.
Last edited by danibitter75; 05-23-2023 at 12:54 PM. Reason: Add Info
Can you use VBA?
Yes...can be VBA.
VBA solution with UDF attached. Function LookupAsset.
Hi to all!
One option could be:
Check file. Blessings!PHP Code:=LOOKUP(1,0/FREQUENCY(0,D4-INDEX(O3:AD24,,MATCH(D2,M1:AB1,))),INDEX(M3:AB24,,MATCH(D2,M1:AB1,)))
A out-of-context text is a pretext.
Consider adding reputation points to all the people who help you with your question/problem.
That's a brilliant formula, John! A rep for you!
Nice and genius solution. Simple and light. Reputation deserved!
Many thanks!
Thanks for the kind words and rep, josephteh and danibitter75. Blessings!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks