What is a formula that will return a cells value located in a table based on two criteria?

TJS_Salty

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

SoS | Dan Palenchar

Look into INDEX(MATCH(MATCH()) in this post:

https://community.smartsheet.com/discussion/100275/how-to-index-match

SoS | Dan Palenchar

Look into INDEX(MATCH(MATCH()) in this post:

https://community.smartsheet.com/discussion/100275/how-to-index-match

TJS_Salty

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)))))))))))))