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:

How to Index Match

Tamsyn

Jan 27, 2023

Hi there,

Please help, I am not great at formulas!

I have a source Employee file that has all the information I need.

I want to add information from the Employee Info sheet to a training register on a different sheet.

There are several column items I want to bring into my sheet e.g. ID number, Job Title, etc

https://us.v-cdn.net/6031209/uploads/VUXTZMOYK8XW/image.png

I have added a row with headers to reference in the formula.

I want to match the data between the two via the Smartsheet contact or "Consultant" name.

I have used the formula:

=INDEX({Employee Sheet Range 2}, MATCH(Consultant@row, {Employees Range 3}, 0), MATCH("ID No",{Employees Range 4}, 0))

Where Range 2 = all the selected columns e.g. ID, Job Title, Consultant

Where Range 3 = Employee Email/ Consultant column

Where Range 4 - Employee ID number column

When I pull through this it shows the ID number for the relevant person

However when I try to use the same formula in another column and change the Range 4 it changes the answer in the other column to the same value.

Anyone know how to resolve/ get this right?

---

View Post

SoS | Dan Palenchar

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

How to Index Match

Tamsyn

Jan 27, 2023

Hi there,

Please help, I am not great at formulas!

I have a source Employee file that has all the information I need.

I want to add information from the Employee Info sheet to a training register on a different sheet.

There are several column items I want to bring into my sheet e.g. ID number, Job Title, etc

https://us.v-cdn.net/6031209/uploads/VUXTZMOYK8XW/image.png

I have added a row with headers to reference in the formula.

I want to match the data between the two via the Smartsheet contact or "Consultant" name.

I have used the formula:

=INDEX({Employee Sheet Range 2}, MATCH(Consultant@row, {Employees Range 3}, 0), MATCH("ID No",{Employees Range 4}, 0))

Where Range 2 = all the selected columns e.g. ID, Job Title, Consultant

Where Range 3 = Employee Email/ Consultant column

Where Range 4 - Employee ID number column

When I pull through this it shows the ID number for the relevant person

However when I try to use the same formula in another column and change the Range 4 it changes the answer in the other column to the same value.

Anyone know how to resolve/ get this right?

---

View Post

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