Forum Discussion
[Excel 2019 ] What is a more elegant way to solve this problem?
So I have 2 tables like this and want to return the correct value based on the UserID. As you can see there are multiple instances of the same UserID with different cost priority.
I don't have access to the Filter formula in Office365. I tried to brute force this using the INDEX function (which interestingly returns the reference to a cell not its value) and Vlookup (which keeps looking in a given range and stops at the last matched value). To determine how many times a UserID appears I use COUNTIF. To choose which Cost Priority I use MIN and SMALL function.
The [Highest Cost priority] formula looks like this:
My solution works. But I want to know if there is a more elegant and efficient way to achieve the same result. Thank you.
=INDEX(IDTable,MATCH(F2&SMALL(IF(IDTable[UserID]=F2,IDTable[Cost_Priority]),1),IDTable[UserID]&IDTable[Cost_Priority],0),2)
=VLOOKUP(F2&G7,CHOOSE({1,2},IDTable[UserID]&IDTable[Problem_Description],IDTable[Cost in USD]),2,0)
Maybe you want to apply formulas like these.
6 Replies
=INDEX(IDTable,MATCH(F2&SMALL(IF(IDTable[UserID]=F2,IDTable[Cost_Priority]),1),IDTable[UserID]&IDTable[Cost_Priority],0),2)
=VLOOKUP(F2&G7,CHOOSE({1,2},IDTable[UserID]&IDTable[Problem_Description],IDTable[Cost in USD]),2,0)
Maybe you want to apply formulas like these.
OliverScheurich
Thank you! This is what I'm looking for. Using the same functions but with much better approach.
Just one question: when I open your file on my Excel, there are "{" and "}" symbols cover the formula. When I take it out the formula returns as error. As you can see in the screenshot I take out the { } in H7 and this happens, while G7 works.
What is "{" and why does the error occurs? I tried to copy the text in the formula to another cell but it too doesnt work.I'm glad my solution is helpful.
The suggested formulas are arrayformulas. If you don't work with Office365 or 2021 you have to enter arrayformulas with ctrl+shift+enter. This automatically puts the curly brackets { } around the formula and the formula works.
If you click into the formula bar of an arrayformula you can click the red cross to leave the formula and keep it working. In the attached file i manually entered a blue arrow to show where to click. If the formula returns an error, press ctrl+shift+enter to initiate the arrayformula.
Mike_Vu_Ngo Perhaps one or more pivot tables can do what you need. Not exactly in the same format, but it's close. See attached.
Riny_van_Eekelen
Hello, thank you! I'm learning Excel and I want to tackle real tasks. I can use Pivot table but in this particular problem I want to keep the format, hence my approach.
