Formula help

New Contributor

I'm looking to have a column automatically update based on a drop down selection and 4 columns of information based. I've tried different sumif/sumifs and can't seem to get it pull correctly. Essentially what I'm trying to do is I have a person name, the drop down is 4 different rate types and the columns are the 4 different rate based on those drop downs. So if for Jane Doe said Rate 1 I would want my rate column to automatically update based on the rate 1 column I have and if Jane Doe is picked rate 2 I'd want the rate to automatically update based on the rate 2 column I have.

6 Replies
With your permission, if I can recommend you, add a file (without sensitive data) to your project.
Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.
At the same time, it is much easier for someone who wants to help to understand the subject.
A win-win situation for everyone.
Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not always in the case of Excel, on the contrary in some cases.

* Knowing the Excel version and operating system would also be an advantage.
Finally, please consider why does one of the helpers still have to prepare the file with your request?

Thank you for your understanding and patience

I know I don't know anything (Socrates)


Perhaps you may illustrate this by sample file not to build data model based on the description and applying some assumptions which could be wrong.

@Sergei Baklan I've attached a generic example. In the summary tab I would like column G to automatically update to the correct rate based on the employee name and the drop down in column F. In the look up tab i have the rates in columns G-J

best response confirmed by carmenromo (New Contributor)


Thank you. Not sure on which version of Excel you are, this formula

=INDEX('Look Up'!$G$2:$J$11, MATCH($B4,'Look Up'!$B$2:$B$11,0), MATCH($F4,'Look Up'!$G$1:$J$1,0))

shall work in any case, just adjust your ranges for actual file. Please check attached.

Thank you so much!!!

@carmenromo , you are welcome