VLOOKUP referencing multiple criteria in another sheet

[mcscot74]

Hi everyone,

I'm hoping someone can help with this one - this is my first post so please have patience!I
have a spreadsheet representing boreholes and where the intersection is for the weathering horizon. I'd like to be able to reference a water level table (on a different sheet) and return AWT, NWT,or BWT (in column D) on the Borehole sheet depending where the weathering horizon sits for each different field.
The borehole sheet is like this:
A B C D
Bore hole Field Weathering_Horizon Water Table
BH_001 East1 400 BWT (Below Water Table)
BH_002 East3 600 AWT (Above Water Table)
BH_003 East4 480 NWT (Near water table)
etc

The Reference table:
A B C D E F
Field Status_AWT Original Water Table Near Water Table Current Water table Below Water table
East1 AWT 494 NWT 477 BWT
East2 AWT 485 NWT 470 BWT
East3 AWT 480 NWT 479 BWT
East4 AWT 495 NWT 481 BWT

Referencing the Above Water table values seems to work but the formula runs into a #VALUE ! error when trying to allocate the "NWT" then BWT will not come through. It appears finding the value between the AWT & BWT is where the issue is but I can't seem to locate it. The data seems ok however I am new to using these functions so hopefully a simple fix.

I am using the following formula to try and reference between the AWT & BWT values to get the NWT where applicable:

VLOOKUP(B2,Water_table!$A$1:$F$11,IF(calcs!C2>=Water_table!$C$1$:$C$11,2,IF(AND(calcs!C2<Water_table!$C$1:$c$11,calcs!c2>Water_table!$E$1:$E$11,Water_table!$D$2:$D$11),IF(calcs!C2<=Water_table!$E$1:$E$11,6))),False)


If anyone could point me in the right direction it would be most appreciated

Thanks
Scott</Water_table!$C$1:$c$11,calcs!c2>

 

[jarjarbingie]

Hi everyone,

I'm hoping someone can help with this one - this is my first post so please have patience!I
have a spreadsheet representing boreholes and where the intersection is for the weathering horizon. I'd like to be able to reference a water level table (on a different sheet) and return AWT, NWT,or BWT (in column D) on the Borehole sheet depending where the weathering horizon sits for each different field.
The borehole sheet is like this:
A B C D
Bore hole Field Weathering_Horizon Water Table
BH_001 East1 400 BWT (Below Water Table)
BH_002 East3 600 AWT (Above Water Table)
BH_003 East4 480 NWT (Near water table)
etc

The Reference table:
A B C D E F
Field Status_AWT Original Water Table Near Water Table Current Water table Below Water table
East1 AWT 494 NWT 477 BWT
East2 AWT 485 NWT 470 BWT
East3 AWT 480 NWT 479 BWT
East4 AWT 495 NWT 481 BWT

Referencing the Above Water table values seems to work but the formula runs into a #VALUE ! error when trying to allocate the "NWT" then BWT will not come through. It appears finding the value between the AWT & BWT is where the issue is but I can't seem to locate it. The data seems ok however I am new to using these functions so hopefully a simple fix.

I am using the following formula to try and reference between the AWT & BWT values to get the NWT where applicable:

VLOOKUP(B2,Water_table!$A$1:$F$11,IF(calcs!C2>=Water_table!$C$1$:$C$11,2,IF(AND(calcs!C2<water_table!$c$1:$c$11,calcs!c2>Water_table!$E$1:$E$11,Water_table!$D$2:$D$11),IF(calcs!C2<=Water_table!$E$1:$E$11,6))),False)


If anyone could point me in the right direction it would be most appreciated

Thanks
Scott</water_table!$c$1:$c$11,calcs!c2>

not sure if I got the requirement correctly but you can try this:

=IF($C2>=INDEX(Water_Table!$C$2:$C$11,MATCH($B2,Water_Table!$A$2:$A$11,0)),"AWT",IF($C2>=INDEX(Water_Table!$E$2:$E$11,MATCH($B2,Water_Table!$A$2:$A$11,0)),"NWT","BWT"))

 

[mcscot74]

Hi jarjarbingie,

Thanks heaps for this - works a treat!

Cheers,

Scott