Forum Discussion
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 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
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
6 Replies
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))
