Forum Discussion
Nested If function with multiple criteria
Hello,
consider the following screenshot:
A table contains the price range in ascending order in columns A and B. Across row 1 is a list or the areas. The body of the table contains the name (or email) of the person to be contacted if the price is in that range in that area.
In columns I and J are a few prices and areas and in column K the respective name is looked up with the formula
=INDEX($C$2:$F$7,MATCH(I2,$A$2:$A$7,1),MATCH(J2,$C$1:$F$1,0))
The first Match function looks for a row where the price is less than or equal to the value in column I. The second match looks for an exact match for the area the header row of the table.
In words: find the row in column A that is smaller than or equal to the price, then find the column that has the exact Area and return the cell where that row and that column cross.
This setup is much easier to maintain than nested IF functions.