excelexcel-2016

Finding a value by two criteria: by value and range in Exel

I am trying to find value "profile" in Excel 2016 formula on sheet 2 from the table on sheet 1 by two criteria:

Frame number and elevations range.

Is it possible to use the command Lookup or some similar to find it? I understand that you first need to find the frame number, then the level that falls within the range.

sheet 1 enter image description here

0 1 2 3
FRAME No. EL.1 EL.2 PROFILE
32 3300 5600 400x200x10x14
32 5600 8400 300x100x10x12
36 3300 5600 400x200x10x14
36 5600 8400 300x100x10x12
40 3300 5600 400x200x10x14
40 5600 8400 300x100x10x12
44 3300 5600 400x200x10x14
44 5600 8400 300x100x10x12

sheet 2 enter image description here

0 1 2
FRAME No. elevation PROFILE
32 3675 400x200x10x14
32 7000 300x100x10x12
40 3675 400x200x10x14
44 3675 400x200x10x14

I found the frame number values but I have no idea how to set the second search criterion by range elevations.

Solution

  • This is the formula in I2 (dragdown) using the INDEX-MATCH functions
    Add equal sign to comparisons if needed.

    =INDEX(D$2:D$9,MATCH(1,(G2=A$2:A$9)*(H2>B$2:B$9)*(H2<C$2:C$9),0))

    enter image description here