IF (AND) FORMULAS WITH MULTIPLE CRITERIAS

New Contributor

I am trying to create an IF statement with multiple criteria. 

 

I have created a list of programs, there are 9 (on Sheet1), each has a different cost. I was able to figure out how to populate the fee determined by the program using the list function. Cell E2 you can use the dropdown and choose a program and the fee will populate in cell H7.  

 

My dilemma is on Cell H8.

Each program has a cost for residential fee or non-residential fee. Each fee is different.  

For instance, Program 1: residential fee is $7378 and the non-residential fee is $4114.  Which I was able to figure out on cell L8 as a test. However, I am unable to include the reset of the residential/non-residential fee by the program on the same cell.  

The goal is to populate the correct fee based on the program and residential fee type in Cell F3. 

 

For example, if a student chooses Program 1 (Cell E2), and chooses Other for cell F3, in H8 it should populate the $4114 amount. 

 

Any help would be awesome. 

 

3 Replies

@TQ-PGI 

In H7:

=VLOOKUP(E2,Sheet1!$A$2:$E$10,2,FALSE)

In H8:

=VLOOKUP(E2,Sheet1!$A$2:$E$10,IF(F3="Other",5,4),FALSE)

Thank you. I will try that. Looks like I was way off on the formula. I appreciate your assistance.

@TQ-PGI 

 

formula in H8: 

=IF(F3<>"Other",VLOOKUP('FA BUDGET WORKSHEET'!$E$2,Sheet1!$A$2:$E$10,4,0),VLOOKUP('FA BUDGET WORKSHEET'!$E$2,Sheet1!$A$2:$E$10,5,0))

 

cheers