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

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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!

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!