Referencing a cell that references a table to provide result from the table.

[jtmjtm]

Hi All,

Apologies for the dreadful title, I'm not sure how to explain this succinctly, part of the reason I can't find a solution online for what I'm sure is a simple problem I'm having.

I'll explain the setup first:
In Sheet 1 I have a basic layout in which Column A is the name of a business, Column B is how many hours I worked, Column C is the hourly rate the business pays, and Column D is the amount I'm owed for the hours I've worked.
In Sheet 2 I have a 2 Column table, Column A is the name of the business and Column B is the hourly rate they pay.

I have set up the cells in Column A of Sheet 1 to provide a dropdown list to select a business name from the table in sheet 2, now I want to have Sheet 1 Column C look at Column A and show the rate associated with the business that is provided in Sheet 2 Column B.

So in short terms 1-A1 selects from 2-A1 through 2-A20 in a dropdown and I would like 1-C1 to reference 1-A1 and show the relevant number from 2-B1 through 2-B20.

The only way that I have been able to do this so far is through multitudes of nested IF statements, and I was wondering if there was an easier way to do this that I've just completely missed.

Thank you in advance for any help.

 

[Fluff]

Hi & welcome to MrExcel
How about
=INDEX(sheet2!A1:B20,MATCH(A1,sheet2!A1:A20,0),2)

 

[jtmjtm]

Thank you very much, that worked perfectly.

 

[Fluff]

You're welcome