Search code examples
google-sheetsexcel-formulagoogle-sheets-formula

Find the value based on combination of different columns and row

I have created a sheet, that anyone can access (if you can copy and make the edit in yours, then that would be appreciated) https://docs.google.com/spreadsheets/d/1rWDpWSROslNLzgMLi7P4PSqFZwRsA-kJxvhqfBoFP-4/edit#gid=0 In that sheet, I am trying to calculate a value based on the data in the different sheet there. Below, you can see my "Expense" sheet where I am looking to get a calculated value in column D (shaded blue color) based on selected values in A, B, and C.

Expense calculation sheet

And data is in the sheet Roles-location-salary. Please note, in that sheet, the salary values are per year, but in the "Expense" sheet I need to have it divided by 12 to get the monthly value.

data values

I tried taking inspiration from Vlookup, but could not get it to work. Any help will be appreciated as to what the formula could be?

Solution

  • Suggestion

    You can try a combination of IF,VLOOKUP,HLOOKUP, & MATCH formulas to achieve your goal:

    Method

    [UPDATED]

    =IF(VLOOKUP(B2,'Roles-location-salary'!A:Q,2,FALSE) = A2, HLOOKUP(C2,'Roles-location-salary'!A:Q,MATCH(B2, 'Roles-location-salary'!A:A,0),FALSE),'Roles-location-salary'!R1)/12

    Demonstration

    enter image description hereThe value returned was divided by 12 to get the monthly value

    References