Mar 06 2018
06:58 AM
- last edited on
Jul 25 2018
11:16 AM
by
TechCommunityAP
Mar 06 2018
06:58 AM
- last edited on
Jul 25 2018
11:16 AM
by
TechCommunityAP
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 ID"). I have created a reference table that contains location, depth and geology.
I have also created an output table which I would like to automatically generate a geology for a given depth within a certain location. Column I, "GEOLOGY", is the column that I would like to populate.
I have tried using a simple VLOOKUP. This either becomes confused or only references BH1001 samples. I am looking for a more complex function, however I am unable to make this work. Any help will be much appreciated.
Thanks,
Oliver
Mar 06 2018 07:35 AM
Hi,
What is the logic for depth? Sample depth shall be between Depth Top and Depth Base?
Mar 06 2018 07:43 AM
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
Mar 06 2018 08:04 AM
SolutionAs 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
Mar 06 2018 08:13 AM
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)
Mar 06 2018 08:19 AM
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))
Mar 06 2018 08:28 AM
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
Mar 06 2018 08:04 AM
SolutionAs 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