SOLVED

Excel Lookup function with varying criteria

Copper Contributor

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

 

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

best response confirmed by null null (Copper Contributor)
Solution

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

 

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)

 

 

 

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))

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

1 best response

Accepted Solutions
best response confirmed by null null (Copper Contributor)
Solution

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

 

View solution in original post