Forum Discussion

TQ-PGI's avatar
TQ-PGI
Copper Contributor
Nov 10, 2021

IF (AND) FORMULAS WITH MULTIPLE CRITERIAS

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

  • Yea_So's avatar
    Yea_So
    Bronze Contributor

    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

  • TQ-PGI's avatar
    TQ-PGI
    Copper Contributor
    Thank you. I will try that. Looks like I was way off on the formula. I appreciate your assistance.
  • 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)

Resources