New Contributor

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

Re: IF (AND) FORMULAS WITH MULTIPLE CRITERIAS

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)

Re: IF (AND) FORMULAS WITH MULTIPLE CRITERIAS

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

Re: IF (AND) FORMULAS WITH MULTIPLE CRITERIAS

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