Excel tutorials

Two conditions in two columns - find the closest higher values and show the data from the third column

For this Excel tutorial I chose the situation when we have two conditions in two columns and we need to display the value from the third column. For this Excel example, I took the dimensions of PVC windows, which are made according to the non-standard wall opening, which have a certain price based on the width and height of the PVC window. If you look at the picture below, you will also notice that it looks like arrangement of data. Our goal is to enter two conditions ie the width and height of the window or door. The formula should find the equal dimension and if he does not find it necessary to look for the nearest first closest dimension of the window/door and return price from the third column.

Find the same or first larger closest value and return the value from the third column

Find equal or greater first nearest the width and height of the window/door and return the price

In the image above, notice that I used this example as the non-standard window and door dimensions. The formula I have created is long. The formula has a lot of nested Excel functions.

The first condition ('F3' cell) is the width of the PVC window. I have set the dimension of 85 cm to the width of the PVC window. The formula needs to find this dimension in the 'B' column, if it does not find the same dimension then it should find the first larger dimension. In the picture you can notice that the formula found a dimension of 100 cm. The second condition ('G3' cell) is the height of the PVC window 95 cm. So the formula needs to find this value in the 'C' column, if it is not found then the formula needs to look for the first higher value. Notice that the formula found a dimension of 100 cm. Ultimately the formula needs to return the price of the PVC window from the 'D' column.

ARRAY formula in 'H3' cell is the following:
=INDEX($D$3:$D$14;MATCH(MIN(IF($B$3:$B$14=IFERROR(SMALL($B$3:$B$14;COUNTIF($B$3:$B$14;"<"&F3)+1);LARGE($B$3:$B$14;COUNTIF($B$3:$B$14;">"&F3)+1));ABS($C$3:$C$14-IFERROR(SMALL($C$3:$C$14;COUNTIF($C$3:$C$14;"<"&G3)+1);LARGE($C$3:$C$14;COUNTIF($C$3:$C$14;">"&G3)+1)))));IF($B$3:$B$14=IFERROR(SMALL($B$3:$B$14;COUNTIF($B$3:$B$14;"<"&F3)+1);LARGE($B$3:$B$14;COUNTIF($B$3:$B$14;">"&F3)+1));ABS($C$3:$C$14-IFERROR(SMALL($C$3:$C$14;COUNTIF($C$3:$C$14;"<"&G3)+1);LARGE($C$3:$C$14;COUNTIF($C$3:$C$14;">"&G3)+1))));0))

Another Array formula that you can use the below (This formula will return ERROR if the conditions set values greater than the maximum in the original data. If you are concerned about this number 9.999999999999E+307 then you should know that this is the term for the largest number that Excel can display ).
=INDEX($D$3:$D$14;MATCH(MIN(IF($B$3:$B$14=LOOKUP(9,999999999999E+307;IF($B$3:$B$14>=F3;$B$3:$B$14));ABS($C$3:$C$14-LOOKUP(9,999999999999E+307;IF($C$3:$C$14>=G3;$C$3:$C$14)))));IF($B$3:$B$14=LOOKUP(9,999999999999E+307;IF($B$3:$B$14>=F3;$B$3:$B$14));ABS($C$3:$C$14-LOOKUP(9,999999999999E+307;IF($C$3:$C$14>=G3;$C$3:$C$14))));0))

Of course, there are other formulas that can be used to return data from the third column if two conditions match. This will leave you to study other ways of creating formulas.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.