is this a job for INDEX?

[BrutalLogiC]

Hi I have a table on one sheet with a few columns, including COUNTRY, AREA, ROLE and COST

On another sheet I have the same columns where people can select the country, area, role from a drop down

The sheet with the predetermined area is called called "CTC", some example cells:
C7 = USA
D7 = Florida
G7 = Electrician... then rest of table has lots of different roles
Z7 = the cost of the electrician working in Florida, USA

then on my main sheet where people can select the data I want people to select country, area and role then the cost will automatically update based on the data from the "CTC" sheet

Hopefully that makes sense... perhaps sumifs or index I really don't know

 

[steve the fish]

You could use sumifs if you knew there was only ever one entry for each scenario. Heres one that works if not:

=LOOKUP(2,1/(($C$1:$C$1000="USA")*($D$1:$D$1000="Florida")*($G$1:$G$1000="Electrician")),$D$1:$D$1000)

You can replace the lookup value with cell references if you want.

 

[BrutalLogiC]

You could use sumifs if you knew there was only ever one entry for each scenario. Heres one that works if not:

=LOOKUP(2,1/(($C$1:$C$1000="USA")*($D$1:$D$1000="Florida")*($G$1:$G$1000="Electrician")),$D$1:$D$1000)

You can replace the lookup value with cell references if you want.

In the drop down there are 4 countries, 4 areas and around 30 roles to select from so I don't think this will work.

 

[steve the fish]

Is the dropdown you refer to a data validation dropdown? Just replace "USA" with the cell reference of that dropdown and so on.

 

[BrutalLogiC]

Yep, I replaced them and it seems to work thank you

 

[BrutalLogiC]

Sorry another query please, how could I update the formula below so that if:
H28=CTC!H6 then it multiplies by CTC!I6 ..... or
H28=CTC!H7 then it multiplies by CTC!I7 ..... or
H28=CTC!H8 then it multiplies by CTC!I8 ..... or
H28=CTC!H9 then it multiplies by CTC!I9

=LOOKUP(2,1/((CTC!$C$12:$C$1005=F28)*(CTC!$D$12:$D$1005=G28)*(CTC!$G$12:$G$1005=E28)),CTC!$AB$12:$AB$1005)

 

[steve the fish]

Do you mean multiply the formula by:

VLOOKUP(H28,CTC!$H$6:$I$9,2,0)

?

 

[BrutalLogiC]

Yes I think so!