Forum Discussion
Solved
Excel Lookup function with varying criteria
Hi,
Firstly I will apologise for what I am sure is a simple request.
I am trying to create a VLOOKUP function to generate a geology type for a certain depth within a certain location ("HOLE ...
As variant that could be array (Ctrl+Shift+Enter) formula
=INDEX($D$3:$D$14,MATCH(1,(H14>=$B$3:$B$14)*(H14<$C$3:$C$14)*($G14=$A$3:$A$14),0))
please see attached
Hi,
What is the logic for depth? Sample depth shall be between Depth Top and Depth Base?
Hi Sergei,
Yes sample depth must be between Depth top and Depth base.
I.e. Cell H3 (referring to BH1001 at 1.5m depth) should output "Alluvium".
Thanks,
Oliver
And one more for the collection
=INDEX($D$3:$D$14,AGGREGATE(14,6,(H3>=$B$3:$B$14)*(H3<$C$3:$C$14)*($G3=$A$3:$A$14)*(ROW($D$3:$D$14)-2),1))
Another variants (regular formulas)
=INDEX($D$3:$D$14,SUMPRODUCT((H3>=$B$3:$B$14)*(H3<$C$3:$C$14)*($G3=$A$3:$A$14)*(ROW($D$3:$D$14)-2)))
and
=LOOKUP(2,1/(H3>=$B$3:$B$14)/(H3<$C$3:$C$14)/($G3=$A$3:$A$14),$D$3:$D$14)
As variant that could be array (Ctrl+Shift+Enter) formula
=INDEX($D$3:$D$14,MATCH(1,(H14>=$B$3:$B$14)*(H14<$C$3:$C$14)*($G14=$A$3:$A$14),0))
please see attached
Sergei,
That's brilliant, all three solutions work perfectly. Many thanks for your help! The examples will go straight in my technical library.
Best,
Oliver