VLOOKUP using multiple search criteria

[Terouka]

Hi their,
I have a annoying problem. I have two datasets of a different length. One of them (DATA2) contains soil sample data regarding a soil classification (i.e. if it is either sand, clay...). I have to assigned now this soil classification of DATA2 to some other soil samples of DATA1 under the two conditions that both, the bore hole numbering matches exactly -- and if this is they case -- secondly, the sample depth of DATA1 is in between the TOP and BOTTOM sample depth of DATA2.


A example is attached.


I am super grateful for your help!

[WideBoyDixon]

There are no identical matches between Column A on DATA1 and Column A on DATA2.

WBD

[Terouka]

Ah sry,
have changed the example a bit. Now, sample NSPT-01 (D4) of DATA1 should be referred to as "Silty Clay" automatically based on DATA2. D5 up to D23 also. D24 should next be a "Silty Sand". I just need a lookup function doing this now automatically.

[JohnTopley]

Try

=IFERROR(INDEX(DATA2!$D$2:$D$1000,AGGREGATE(15,6,ROW($A$1:$A$1000)/($A4=DATA2!$A$2:$A$1000)/($B4>=DATA2!$B$2:$B$1000)/($C4<=DATA2!$C$2:$C$1000),1)),"")

[Terouka]

Perfect! This solves the problem.

Many thanks!