Lookup Formula needed

**kingharvest** · 09-12-2012, 04:06 AM

Hi,

I'm trying to create a spreadsheet which calculates someones pay costs for a project. I've attached the workbook to help explain. But what i need it do is as follows:

In column B, you select the persons grade from a data valid list, and then in C you select whether or not you want to calculate their cost based on their Annual Salary or their Hourly Rate. THen depending on whether they choose, FTE or Hours the corresponding rate goes into column D. I've created the spreadsheet, and the data valid lists on a seperate sheet in the workbook, i just can't work out the formula to make it work.

Can anyone help?

**Fotis1991** · 09-12-2012, 04:25 AM

Hi

Maybe, in D3.

=IF(C3="FTE",INDEX('Data List'!$B$2:$B$6,MATCH(Sheet1!B3,'Data List'!$A$2:$A$6,0)),IF(Sheet1!C3="Hours",INDEX('Data List'!$C$2:$C$6,MATCH(Sheet1!B3,'Data List'!$A$2:$A$6,0))))

**Ace_XL** · 09-12-2012, 04:26 AM

Thsi will give you annual rate or hourly rate based on your selection in Column B and Column C

=INDEX(IF(C3="FTE",'Data List'!$B$1:$B$6,'Data List'!$C$1:$C$6),MATCH(B3,'Data List'!$A$1:$A$6,0))

**kingharvest** · 09-12-2012, 04:33 AM

Excellent - thanks ever so much both of you. You've done in 10 minutes what i'd been going over for a day!

**zbor** · 09-12-2012, 04:33 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.