What is a formula that will return a cells value located in a table based on two criteria?
So i want to include an interactive element on my dashboard when my client will chose the borrow amount and the term from two dropdowns. Ideally this would lookup the table on the right and return the corresponding value to the pink cell.
any help is appreciated
Best Answer
-
Look into INDEX(MATCH(MATCH()) in this post:
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Answers
-
Look into INDEX(MATCH(MATCH()) in this post:
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Thanks Dan, I appreciate the feedback. I'll try it out.
I did come up with a workaround even though it's a longer code it does work as intended.
=IF(CONTAINS("Weekly 1 Year", Term@row), INDEX(COLLECT([Weekly 1Year]2:[Weekly 1Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Weekly 3 Year", Term@row), INDEX(COLLECT([Weekly 3Year]2:[Weekly 3Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Weekly 5 Year", Term@row), INDEX(COLLECT([Weekly 5Year]2:[Weekly 5Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Weekly 7 Year", Term@row), INDEX(COLLECT([Weekly 7 Year]2:[Weekly 7 Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Fortnightly 1 Year", Term@row), INDEX(COLLECT([Fortnight 1Year]2:[Fortnight 1Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Fortnightly 3 Year", Term@row), INDEX(COLLECT([Fortnight 3Year]2:[Fortnight 3Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Fortnightly 5 Year", Term@row), INDEX(COLLECT([Fortnight 5Year]2:[Fortnight 5Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Fortnightly 7 Year", Term@row), INDEX(COLLECT([Fortnight 7Year]2:[Fortnight 7Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Monthly 1 Year", Term@row), INDEX(COLLECT([Monthly 1Year]2:[Monthly 1Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Monthly 3 Year", Term@row), INDEX(COLLECT([Monthly 3Year]2:[Monthly 3Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Monthly 5 Year", Term@row), INDEX(COLLECT([Monthly 5Year]2:[Monthly 5Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1), IF(CONTAINS("Monthly 7 Year", Term@row), INDEX(COLLECT([Monthly 7Year]2:[Monthly 7Year]26, [Loan Amount]2:[Loan Amount]26, [Borrow Amount]@row), 1)))))))))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!